Search a string in the definition of any stored procedure in all the user databases

On 14 Apr, 2015

By : Charith Silva

No Comments

Following stored procedure can be used to search any string in the definition of any stored procedure in all the user databases in a given SQL server instance.

USE [master];
GO

CREATE PROCEDURE [dbo].[usp_Search_Definition](@SearchString NVARCHAR(1000))
AS

SET NOCOUNT ON
DECLARE @SQL NVARCHAR(MAX)

IF OBJECT_ID('tempdb..#Search_results ') IS NOT NULL
DROP TABLE #Search_results

CREATE TABLE #Search_results
(
Servername NVARCHAR(128),
db NVARCHAR(128),
objectname NVARCHAR(128),
xtype NVARCHAR(10),
definition NVARCHAR(MAX)
)
 
DECLARE #databases CURSOR FOR
SELECT NAME FROM master..sysdatabases where dbid>4

    DECLARE @database_name varchar(128)

    OPEN #databases
    FETCH #databases INTO @database_name 
    WHILE @@FETCH_STATUS = 0
    BEGIN
       SELECT @SQL = 'INSERT INTO #Search_results  '
       SELECT @SQL = @SQL + 'SELECT @@Servername AS Servername,''' + @database_name+ ''' AS db, o.name,o.xtype,m.definition '  
       SELECT @SQL = @SQL + ' FROM '+@database_name+'.sys.sql_modules m '  
       SELECT @SQL = @SQL + ' INNER JOIN '+@database_name+'..sysobjects o ON m.object_id=o.id'  
       SELECT @SQL = @SQL + ' WHERE [definition] LIKE ''%'+@SearchString+'%''' 

       EXEC(@SQL)
       FETCH #databases INTO @database_name
    END

    CLOSE #databases
    DEALLOCATE #databases
 
SELECT Servername AS SQLServer,db as Database_Name, objectname as Object_Name, Definition
FROM #Search_results
WHERE xtype='P'
ORDER BY db,objectname

DROP TABLE #Search_results

USE [master];

CREATE PROCEDURE [dbo].[usp_Search_Definition](@SearchString NVARCHAR(1000))

SET NOCOUNT ON

DECLARE @SQL NVARCHAR(MAX)

IF OBJECT_ID('tempdb..#Search_results ') IS NOT NULL

DROP TABLE #Search_results

CREATE TABLE #Search_results

(

Servername NVARCHAR(128),

db NVARCHAR(128),

objectname NVARCHAR(128),

xtype NVARCHAR(10),

definition NVARCHAR(MAX)

)

DECLARE #databases CURSOR FOR

SELECT NAME FROM master..sysdatabases where dbid>4

DECLARE @database_name varchar(128)

OPEN #databases

FETCH #databases INTO @database_name

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL = 'INSERT INTO #Search_results '

SELECT @SQL = @SQL + 'SELECT @@Servername AS Servername,''' + @database_name+ ''' AS db, o.name,o.xtype,m.definition '

SELECT @SQL = @SQL + ' FROM '+@database_name+'.sys.sql_modules m '

SELECT @SQL = @SQL + ' INNER JOIN '+@database_name+'..sysobjects o ON m.object_id=o.id'

SELECT @SQL = @SQL + ' WHERE [definition] LIKE ''%'+@SearchString+'%'''

EXEC(@SQL)

FETCH #databases INTO @database_name

END

CLOSE #databases

DEALLOCATE #databases

SELECT Servername AS SQLServer,db as Database_Name, objectname as Object_Name, Definition

FROM #Search_results

WHERE xtype='P'

ORDER BY db,objectname

DROP TABLE #Search_results

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva