Search Particular Text used in Stored Procedure in Sql Server
Posted by Viral Sarvaiya on September 10, 2012
Few days ago I get very tedious job in database,
I have to search all stored procedure which have use one particular table.
I get following 1 option of query from sys table of sqlserver
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%TBL_NAME%' AND ROUTINE_TYPE='PROCEDURE'
SELECT Name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TBL_NAME%'
but this query only table table’s name, if We want particular string not table then this query will fail so below stored procedure will work.
CREATE PROCEDURE [dbo].[sp_search_code] ( @SearchStr varchar(100), @RowsReturned int = NULL OUT ) AS BEGIN SET NOCOUNT ON SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name', CASE WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1 THEN 'Replication stored procedure' WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 THEN 'Extended stored procedure' WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1 THEN 'Stored Procedure' WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1 THEN 'Trigger' WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 THEN 'Table-valued function' WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 THEN 'Scalar-valued function' WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1 THEN 'Inline function' END AS 'Object type', 'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) + '''' AS 'Run this command to see the object text' FROM syscomments c INNER JOIN sysobjects o ON c.id = o.id WHERE c.text LIKE '%' + @SearchStr + '%' AND encrypted = 0 AND ( OBJECTPROPERTY(c.id, 'IsReplProc') = 1 OR OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR OBJECTPROPERTY(c.id, 'IsProcedure') = 1 OR OBJECTPROPERTY(c.id, 'IsTrigger') = 1 OR OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1 ) ORDER BY 'Object type', 'Object name' SET @RowsReturned = @@ROWCOUNT END
Run this query as like below
exec sp_search_code 'SearchText'
You will get require output of list of stored procedure name.
In this Stored Procedure i take one output parameter for check number of rows return by Stored Procedure.
Hope this will help you.
Thanks.
Search Particular Text used in Data of All Tables of database in Sql Server « Code Simplified – Viral Sarvaiya said
[…] Search Particular Text used in Stored Procedure in Sql Server […]