Locating stored procedures (and more) on a SQL Server instance.

Sometimes you need to locate a stored procedure on a SQL Server instance but you are not sure what database it is in. Well, at least I sometimes find myself in that situation. The following script iterates over each of the databases on the current SQL Server instance displaying the database name which contains the stored procedure named (replace #Stored Procedure Name# with the name of the stored procedure you are looking for):

-- Use for stored procedures only.
EXEC sp_MSForEachDB
'USE [?];
DECLARE @databaseName VARCHAR(100);
SELECT @databaseName = SPECIFIC_CATALOG FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = ''#Stored Procedure Name#''
IF @databaseName IS NOT NULL PRINT @databaseName'

Sometimes you know only part of it so the following script uses the LIKE statement which uses % as a wildcard. This can be used wherever the LIKE statement allows (see http://msdn.microsoft.com/en-us/library/ms179859.aspx). For example:

EXEC sp_MSForEachDB
'USE [?];
DECLARE @databaseName VARCHAR(100);
USE [?]; SELECT @databaseName = ''?'' FROM sys.sysobjects
WHERE sys.sysobjects.name LIKE ''#Start Of Stored Procedure Name#%''
AND sys.sysobjects.type = ''P''
ORDER BY sys.sysobjects.name
IF @databaseName IS NOT NULL PRINT @databaseName'

If you want to search for other object types then you need to replace the value compared to sys.sysobjects.type from P to one of the following:

AF = Aggregate function (CLR)
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
FN = Scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued functionIF = In-lined table-function
IT - Internal table
K = PRIMARY KEY or UNIQUE constraint
L = Log
P = Stored procedure
PC = Assembly (CLR) stored-procedure
R = Rule
RF = Replication filter stored procedure
S = System table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = Table function
TR = SQL DML Trigger
TT = Table type
U = User table
V = View
X = Extended stored procedure

This was sourced from the MSDN article for sys.sysobjects (http://msdn.microsoft.com/en-us/library/ms177596.aspx).

Advertisements