Geek Answers Handbook

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

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

(I am adding some code to my system health monitoring and I want to warn about those that are declared in a way where this is not justified.)

Note. I'm not looking for a general text search for "WITH RECOMPILE" - I can already do this, but it will give a false representation for any comments or version literals.

+4
source share
3 answers

Thanks to GSquared on the Central SQL Server Forums , I found it flag is_recompiled in sys.sql_modules .

0
source

For a quick and dirty way, I will use:

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

This is probably not a good idea to use in a real application. If I have a few minutes, I will try to dig a cleaner path. The above also catches procs for which this line is commented out, it uses MS SQL Server specific tables, etc.

+1
source

This uses a query that will search for custom views, stored procedures, and functions for any 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 + '%'
+1
source

More articles:


All Articles