Tip for SQL Server: Find Text Used in SPs, Functions and Table
This tip will help you to find a particular text used in SPs, Functions or tables
Introduction
This tip will help you to solve the issues when someone tells you about where this text is used in your SQL tables/SPs/Functions.
Background
A number of times, my PM told me to find stored procedures/functions where we have used specific text, then make changes to that part. So I have to check in individual SPs and functions for that text.
Using the Code
This is simple code; you have to add this SP in your SQL server database.
CREATE PROCEDURE [dbo].[usp_Find]
(
@vcrSearchString VARCHAR(100)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @vcrSQL VARCHAR(1500)
SELECT @vcrSQL = 'SELECT SO.name as Object,
COUNT(*) as Occurences, ' +
'CASE ' +
' WHEN SO.xtype = ''D'' THEN ''Default'' ' +
' WHEN SO.xtype = ''FN'' THEN ''Function'' ' +
' WHEN SO.xtype = ''F'' THEN ''Foreign Key'' ' +
' WHEN SO.xtype = ''P'' THEN ''Stored Procedure'' ' +
' WHEN SO.xtype = ''PK'' THEN ''Primary Key'' ' +
' WHEN SO.xtype = ''S'' THEN ''System Table'' ' +
' WHEN SO.xtype = ''TR'' THEN ''Trigger'' ' +
' WHEN SO.xtype = ''V'' THEN ''View'' ' +
'END AS TYPE ' +
'FROM dbo.syscomments as SC
JOIN dbo.sysobjects as SO ON SC.id = SO.id ' +
'WHERE PATINDEX(''%' + @vcrSearchString + '%'', SC.text ) > 0 ' +
'GROUP BY SO.name, SO.xtype ' +
'UNION ' +
'SELECT SUBSTRING(SO.name, 1, 50 ) as Object,
1 as Occurances,
''User Table'' as TYPE
FROM SYSOBJECTS as SO
INNER JOIN SYSCOLUMNS as SC on SC.id = SO.id
WHERE SC.name LIKE ''%' + @vcrSearchString + _
'%'' AND SO.xtype =' + '''U'''
EXECUTE( @vcrSQL )
SET NOCOUNT OFF
END
How to use this Query in DB:
EXEC Usp_Find 'tblX'
GO;
EXEC Uso_Find 'Insert into tblY'
Points of Interest
SQL Server makes it easy.