how to find stored procedures in sql server 2008 or sql server 2005 across all databases

As much as I hate searching for objects or not having complete information to troubleshoot an issue sometimes I have to go search for a stored procedure / function in a database server.

To find which database contains a stored procedure I am looking for on a server I run the following script

-- SEARCH FOR OBJECT
-- FIND A ROUTINE / PROCEDURE / FUNCTION IN ALL DATABASES ON SERVER.
EXEC sp_msforeachdb N'USE [?];
SELECT
@@SERVERNAME + '' | '' + SUSER_SNAME() + '' | ''+ DB_NAME() + '' | '' + CONVERT(VARCHAR(20),getdate(),120) as [SOURCE],
ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME ,
''[''+ CAST(@@SERVERNAME AS VARCHAR(100))+ ''].[''+ ROUTINE_CATALOG + ''].[''+ ROUTINE_SCHEMA + ''].[''+ ROUTINE_NAME + '']'' as [FQN]
FROM [?].INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME LIKE ''%myROUTINEName%'';
'
GO

I combine this with registering a group of servers in management studio, and in one quick step I can search for a procedure or function in any set of servers and databases.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s