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)