3

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?

7

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;
  • 1
    Hey doesn't the Brent Ozar's & Team's sp_blitz stored procedure find that for you when you run it ? – Shanky Jun 8 '18 at 16:28
  • 1
    @Shanky sure, if you're into that sort of thing ;) – Erik Darling Jun 8 '18 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.