Searching *Inside* Stored Procedures

I’ve recently inherited a large BizTalk system and needed a quick way to determine which custom stored procedures accessed which tables/views and called other sprocs. Thanks to my co-worker Shaun, I now have the power of the INFORMATION_SCHEMA views at my disposal!

Information Schema Views

Information schema views provide an internal, system table-independent view of SQL Server metadata. They provide information on tables, views, stored procedures to name but a few objects. The views included in SQL Server 2005 comply with the ANSI SQL-92 standard definition, so any queries can (theoretically) be taken and executed on Oracle, or DB2, etc. More information can be found online at http://msdn2.microsoft.com/en-us/library/ms186778.aspx

Searching within Stored Procedures

To search for – or within – stored procedures, you need to work with the INFORMATION_SCHEMA.ROUTINES view, the following sample returns all stored procedures where the name contains ‘BizTalkServerApplication’:

–– Change to the BizTalk Message Box database.
USE BizTalkMsgBoxDb
GO
–– Search for sprocs with ‘BizTalkServerApplication’ in the name.
SELECT ROUTINE_NAME,
CONVERT(VARCHAR(8), created, 3) + ‘ ‘ + CONVERT(VARCHAR(8), created, 108) AS ‘Created Date’,
CONVERT(VARCHAR(8), last_altered, 3) + ‘ ‘ + CONVERT(VARCHAR(8), last_altered, 108) AS ‘Last Altered On’
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME LIKE ‘%BizTalkServerApplication%’
ORDER BY 1

To search within stored procedures, simply change your WHERE clause to use the ROUTINE_DEFINITION column (Note: the ROUTINE_DEFINITION column only includes the first 4000 characters of the T-SQL statements that created the stored procedure). The following sample returns all stored procedures that contain the text ‘SuspendedQ’:

–– Change to the BizTalk Message Box database.
USE BizTalkMsgBoxDb
GO
–– Search for sprocs that contain the text ‘SuspendedQ’ in the name.
SELECT ROUTINE_NAME,
CONVERT(VARCHAR(8), created, 3) + ‘ ‘ + CONVERT(VARCHAR(8), created, 108) AS ‘Created Date’,
CONVERT(VARCHAR(8), last_altered, 3) + ‘ ‘ + CONVERT(VARCHAR(8), last_altered, 108) AS ‘Last Altered On’
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ‘%SuspendedQ%’
ORDER BY 1

Advertisements

One thought on “Searching *Inside* Stored Procedures

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