How to find WITH RECOMPILE metadata in SQL Server (2005)?

Question

How do you find which SPs are declared WITH RECOMPILE, either in INFORMATION_SCHEMA, sys.objects or some other metadata?

(I'm adding some code to my system health monitoring and want to warn on ones which are declared that way where it is not justifiable.)

Note: I'm not looking for general text search for 'WITH RECOMPILE' - I can already do that, but it will give false positive for any commented or literal versions of the text.

Answer 1

For a quick and dirty way I would use:

SELECT
     o.name
FROM
     syscomments c
INNER JOIN sys.objects o ON
     o.object_id = c.id
WHERE
     c.text LIKE '%WITH RECOMPILE%'

That's probably not a good idea for use in an actual application though. If I have a few minutes I'll try to dig up a cleaner way. The above will also catch procs that have that string commented out, it uses the MS SQL Server specific table syscomments, etc.

Answer 2

Here's a query I use which will search user defined views, stored procedures and functions for any given search string:

DECLARE @strSearch varchar(50)
SELECT @strSearch = 'my search string'

SELECT
    so.[Name],
    OBJECT_ID(so.[Name]) 
FROM
    [sysobjects] so
JOIN
    syscomments sc
ON
    sc.[ID] = OBJECT_ID(so.[Name])
WHERE 
    (so.[xtype] = 'FN' OR so.[xtype] = 'P' OR so.[xtype] = 'V') AND
    so.[category] = 0 AND
    sc.encrypted = 0 AND
    sc.[text] like '%' + @strSearch + '%'

Answer 3

Thanks to GSquared on SQL Server Central Forums, I found it, there is a flag called is_recompiled in sys.sql_modules.