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

• Cade Roux

  Ten Centuries

  Points: 1346

  More actions

  January 12, 2009 at 1:01 pm

  #203957

  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.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  January 12, 2009 at 1:04 pm

  #925525

  There's an is_recompiled column in sys.sql_modules. Is that what you're looking for?

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• Cade Roux

  Ten Centuries

  Points: 1346

  More actions

  January 12, 2009 at 1:15 pm

  #925534

  Sure looks like it - thanks so much!