Search in MSSQL objects

    -- To search in MSSQL programmable objects (stored procedures, functions, views, triggers)

 

CREATE PROCEDURE SearchInAllDatabaseObjects

    @SearchText NVARCHAR(255)

AS

BEGIN

    SET NOCOUNT ON;

   

    -- Search in programmable objects (stored procedures, functions, views, triggers)

    SELECT

        SCHEMA_NAME(o.schema_id) + '.' + o.name AS FullObjectName,

        CASE o.type

            WHEN 'P' THEN 'Stored Procedure'

            WHEN 'FN' THEN 'Scalar Function'

            WHEN 'IF' THEN 'Inline Table Function'

            WHEN 'TF' THEN 'Table Function'

            WHEN 'TR' THEN 'Trigger'

            WHEN 'V' THEN 'View'

        END AS ObjectType,

        o.type AS TypeCode,

        o.create_date AS CreatedDate,

        o.modify_date AS ModifiedDate

    FROM

        sys.sql_modules m

    INNER JOIN

        sys.objects o ON m.object_id = o.object_id

    WHERE

        m.definition LIKE '%' + @SearchText + '%'

        AND o.type IN ('P', 'FN', 'IF', 'TF', 'TR', 'V')

   

    UNION ALL

   

    -- Search in table/column names

    SELECT

        SCHEMA_NAME(t.schema_id) + '.' + t.name AS FullObjectName,

        CASE t.type

            WHEN 'U' THEN 'Table (User)'

            WHEN 'S' THEN 'Table (System)'

        END AS ObjectType,

        t.type AS TypeCode,

        t.create_date AS CreatedDate,

        t.modify_date AS ModifiedDate

    FROM

        sys.tables t

    WHERE

        t.name LIKE '%' + @SearchText + '%'

        OR EXISTS (

            SELECT 1

            FROM sys.columns c

            WHERE c.object_id = t.object_id

            AND c.name LIKE '%' + @SearchText + '%'

        )

   

    ORDER BY

        ObjectType, FullObjectName;

END

GO

To make it work for all databases as system stored procedure use the following script

System procedures must be in master and prefixed with sp_

sp_ms_marksystemobject only works if the procedure exists in master.

USE master;
GO

CREATE PROCEDURE sp_SearchInAllDatabaseObjects
    @SearchText NVARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;

    -- Search in programmable objects
    SELECT 
        SCHEMA_NAME(o.schema_id) + '.' + o.name AS FullObjectName,
        CASE o.type
            WHEN 'P' THEN 'Stored Procedure'
            WHEN 'FN' THEN 'Scalar Function'
            WHEN 'IF' THEN 'Inline Table Function'
            WHEN 'TF' THEN 'Table Function'
            WHEN 'TR' THEN 'Trigger'
            WHEN 'V' THEN 'View'
        END AS ObjectType,
        o.type AS TypeCode,
        o.create_date AS CreatedDate,
        o.modify_date AS ModifiedDate
    FROM sys.sql_modules m
    INNER JOIN sys.objects o ON m.object_id = o.object_id
    WHERE m.definition LIKE '%' + @SearchText + '%'
      AND o.type IN ('P','FN','IF','TF','TR','V')

    UNION ALL

    -- Search in tables/columns
    SELECT 
        SCHEMA_NAME(t.schema_id) + '.' + t.name AS FullObjectName,
        CASE t.type
            WHEN 'U' THEN 'Table (User)'
            WHEN 'S' THEN 'Table (System)'
        END AS ObjectType,
        t.type AS TypeCode,
        t.create_date AS CreatedDate,
        t.modify_date AS ModifiedDate
    FROM sys.tables t
    WHERE t.name LIKE '%' + @SearchText + '%'
       OR EXISTS (
            SELECT 1 
            FROM sys.columns c 
            WHERE c.object_id = t.object_id 
              AND c.name LIKE '%' + @SearchText + '%'
        )
    ORDER BY ObjectType, FullObjectName;
END;
GO
EXEC sp_ms_marksystemobject 'sp_SearchInAllDatabaseObjects';


-- Example usage:
-- EXEC sp_SearchInAllDatabaseObjects 'YourSearchTextHere';