Some Ways To Look For Things in SQL Server Modules

If you have a medium to large database in SQL Server, with lots of stored procedures, functions or views, it can sometimes be difficult to keep track of whether you have used certain options (such as RECOMPILE).  The queries below show some different things you can discover by querying sys.sql_modules and sys.objects.

    -- List all modules in the current database
    SELECT OBJECT_NAME(sm.[object_id]) AS [object_name], o.[type], 
           o.type_desc, sm.[definition]
    FROM sys.sql_modules AS sm
    INNER JOIN sys.objects AS o 
    ON sm.[object_id] = o.[object_id]
    ORDER BY o.[type];
    
    -- Look for modules that use a specific term (Method 1)
    SELECT OBJECT_NAME(sm.[object_id]) AS [Object Name], sm.[definition]
    FROM sys.sql_modules AS sm
    WHERE sm.[definition] LIKE '%SQLCONTENT01%';
    
    -- Look for modules that use a specific term (Method 2)
    SELECT OBJECT_NAME(id) AS [Object Name], [text]
    FROM sys.syscomments 
    WHERE [text] LIKE '%SQLCONTENT01%'
    
    -- Find stored procedures that have WITH RECOMPILE set
    SELECT OBJECT_NAME(sm.[object_id]) AS [Object Name], 
           sm.[definition], sm.is_recompiled
    FROM sys.sql_modules AS sm
    INNER JOIN sys.objects AS o 
    ON sm.[object_id] = o.[object_id]
    WHERE sm.is_recompiled = 1
    AND o.type_desc = 'SQL_STORED_PROCEDURE';

This entry was posted in SQL Server 2008. Bookmark the permalink.

3 Responses to Some Ways To Look For Things in SQL Server Modules

  1. Unknown says:

    http://www.salesid.com and http://www.saletoo.com Are Top Wholesale: Belts Caps Clothing Glasses Handbags Shoes Wallets Watches.

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