As a DBA we are often tasked to analyze tables, views and procedures that are unfamiliar such as a vendor provided database. This is a simple script working with SQL Sever to find a stored procedure containing a text string. It’s very useful for auditing bad procedures or looking for similarly named fields when you find yourself stuck.
SELECT pr.[name], smod.[definition]
FROM sys.all_sql_modules AS smod
INNER JOIN sys.procedures AS pr
ON pr.[object_id] = smod.[object_id]
WHERE [definition] LIKE '% bad code like goto %'You might be familiar with using “sys.objects” to look for an object name like a ‘schema’, ‘table’ or ‘view’. Here we are searching the procedure text to find a string match anywhere through the actual procedure T-SQL statement (string). Basically, if you use the “script as” option in SSMS this is what were searching.
Choose text From One User Defined Procedure
We start with the ‘sys.all_sql_modules’ where the definition is stored but we need the procedure name so we join it with ‘sys.procedures’ using the object_id. We can add to the statement to specify only user created procedures (p) and use the name if we know what proc were looking for.
WHERE [definition] LIKE '%comments inside the text%'
AND pr.[name] = 'pr_someprocname'
AND pr.[type] = 'P'Get the SQL Object ID Using Object Name
SELECT OBJECT_ID(N'pr_someprocname') AS 'Object ID'; Search All User Database Procedures
We can again defy the Vogon instruction and use our undocumented stored procedure sp_msforeachdb to search “all” of the databases and their procedures for our search text.
“If you have Lotsa DB’s and procedures,.. this will be SLOW!”
Temp Table To Store Results
Since were bouncing from Database to Database we need to store the result in a temp table, a table variable doesn’t (db tales com) seem to work since the transaction completes when the procedure executes on a new database.
We create a temp table, wrap the statement in our ‘sp_msforeachdb’ procedure and query the temp table at the end. You will also note that I added the database name [?] to the temp table and I am excluding the system DB’s using an IF statement.
Note: pay attention to the single quotes inside “sp_msforeachdb” procedure.
IF OBJECT_ID('tempdb..##getproctext', 'U') IS NOT NULL
BEGIN DROP TABLE ##getproctext END
CREATE TABLE ##getproctext (
[DB] sysname NULL,
[name] VARCHAR(200) NULL,
[definition] TEXT NULL)
exec sp_msforeachdb '
USE [?]
DECLARE @dbname sysname = DB_NAME()
--Check the current db name and skip if a system DB
IF @dbname NOT IN (''master'',''model'',''tempdb'',''msdb'')
BEGIN
INSERT INTO ##getproctext ([DB], [name], [definition])
SELECT
@dbname AS [DB],
pr.[name],
smod.[definition]
FROM sys.all_sql_modules AS smod
INNER JOIN sys.procedures AS [pr]
ON pr.[object_id] = smod.[object_id]
WHERE [pr].[type] = ''P''
AND [definition] LIKE ''%some search text%''
END
'