How to find Stored Procedures With Recompile?

Question

In my databases I have some Stored procedure with recompile

The way I find them is:

SELECT OBJECT_NAME(ID)AS SP_NAME,* 
FROM SYSCOMMENTS
WHERE TEXT LIKE '%WITH RECOMPILE%'

when I look at:

select * from sys.procedures

I find no indication of recompiles.

Is there a better or more elegant way to find the with recompile stored procedures?

Answer 1

Per database, you can do this:

SELECT          DISTINCT
                DB_NAME() AS DBName, 
                SO.name AS SPName, 
                SM.is_recompiled, 
                ISR.SPECIFIC_SCHEMA
FROM            sys.sql_modules AS SM
LEFT OUTER JOIN master.sys.databases AS sDB
    ON SM.object_id = DB_ID()
LEFT OUTER JOIN sys.sysobjects AS SO
    ON  SM.object_id = SO.id
    AND SO.type = 'P'
LEFT OUTER JOIN INFORMATION_SCHEMA.ROUTINES AS ISR
    ON  ISR.ROUTINE_NAME = SO.name
    AND ISR.SPECIFIC_CATALOG = DB_NAME()
WHERE           SM.is_recompiled = 1;