Wednesday, March 19, 2014

Analyzing SQL Server Modules

There might be situations where you find mysterious behaviours of your SQL Server Stored Procedures or Functions probably while deploying a lengthy script with multiple creations or alterations of SPs or Functions. In such cases a DROP statement of one stored procedure could accidentally get included in the CREATE script of another one, possibly if you had forgotten the batch terminator GO. Recently at my workplace we were puzzled to see a stored procedure getting disappeared hours after deployment. After some hazzle, search on the internet, I posted this question on StackOverflow, I was able to get a good suggestion from this guy, to check on tracing the usage of the stored procedure by suggesting the following query on this post.

By querying the sys.sql_modules we'll be able to get the definitions of all stored procedures and functions.

SELECT o.type_desc AS ROUTINE_TYPE
        ,o.[name] AS ROUTINE_NAME
        ,m.definition AS ROUTINE_DEFINITION
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
WHERE m.definition LIKE '%search term here%'
The search term could be partial name of the stored procedure in question and the "Definition" field will give the complete script on the creation of that SP or Function.