Archive for the ‘Stored Procedures’ Category

How to Search All Stored Procedures for a Specific Keyword?

You have been assigned to support a SQL Server instance that your team has been given ownership of through recent re-organizations within your company. You need to search for the stored procedures that reference a specific table or keyword. There are no documentations and you do not have intimate or domain knowledge about the databases as they were created by developers that are no longer with the company or third-party software vendors that are no longer in business. Yes. It is a very tough job. Let’s get it done.

To search all the stored procedures in a database for a specific keyword:

-- search all stored procs in a database for a specific keyword
SELECT DB_NAME() AS [Database Name],
	OBJECT_NAME(object_id) AS [Stored Proc Name], 
	OBJECT_DEFINITION(object_id) as [Stored Proc Definition]
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%Keyword_Search%'

What if you need to search all the stored procedures in all databases in a SQL Server instance for a specific keyword? Use the following script instead:

-- search all stored procs in all databases for a specific keyword
DECLARE @dbname nvarchar(123)
, @id int
, @max int
, @cmdSearch nvarchar(max)

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

CREATE TABLE #db_list
(
	id int identity (1,1)
	, dbname nvarchar(123)
);

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

CREATE TABLE #dbs_storedprocs
(
	dbname nvarchar(123),
	storedproc_name nvarchar(250),
	storeproc_definition nvarchar(max)
);


INSERT INTO #db_list
SELECT db.name
FROM sys.databases db
WHERE db.state = 0;

SELECT @id = 1, @max = max(id)
FROM #db_list

WHILE (@id <= @max)
BEGIN
	SELECT @dbname = dbname
	FROM #db_list
	WHERE id = @id;

	SET @cmdSearch = 'USE ' +@dbname+
		' SELECT DB_NAME(), 
				OBJECT_NAME(object_id), 
				OBJECT_DEFINITION(object_id) 
		 FROM sys.procedures
		 WHERE OBJECT_DEFINITION(object_id) LIKE ''%Keyword_Search%''';

	INSERT INTO #dbs_storedprocs
	EXEC (@cmdSearch);

	SET @id = @id + 1;
END

SELECT dbname  AS [Database Name],
	storedproc_name  AS [Stored Proc Name],
	storeproc_definition AS [Stored Proc Definition]
FROM #dbs_storedprocs