Some Ways To Look For Things in SQL Server Modules

Quote from http://glennberrysqlperformance.spaces.live.com/Blog/cns!45041418ECCAA960!2218.entry:

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], 
    FROM sys.syscomments 
    WHERE  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';