erictopia.com

Searching SQL Functions, Stored Procedures, and Views

Apr 07

EricDevelopment, SQL SQL 2 Comments

Have you ever needed to find every function, stored procedure, or view that contains a specific string? How about when you need to reference some sql code you’ve already written, but can’t remember where it is? Well this handy little script, which could be turned into a stored procedure, will search through all those functions, stored procedures, and views to locate your search string.

To use, simply change the value of the @search variable and execute. Your output will include the type (function, stored procedure, or view), the schema, and the name of the object.

DECLARE @search AS VARCHAR(50)

-- Set this variable
SET @search = '@@FETCH_STATUS'

-- The search
SET @search = '%' + @search + '%'
SELECT  DISTINCT
     CASE WHEN OBJECTPROPERTY(C.id, 'IsProcedure') = 1
             THEN 'Stored Procedure'
             WHEN OBJECTPROPERTY(C.id, 'IsView') = 1 THEN 'View'
             WHEN OBJECTPROPERTY(C.id, 'IsInlineFunction') = 1
                  OR OBJECTPROPERTY(id, 'IsScalarFunction') = 1
                  OR OBJECTPROPERTY(id, 'IsTableFunction') = 1 THEN 'Function'
             ELSE 'Unknown'
        END AS [Type],
        S.NAME AS [Schema],
        OBJECT_NAME(C.id) AS [Name]
FROM    syscomments C
        INNER JOIN Sys.Schemas S ON OBJECTPROPERTY(C.id, 'SchemaId') = S.Schema_ID
WHERE   1 LIKE @search
ORDER BY [Type],
        S.NAME,
        OBJECT_NAME(C.id)