Recompile stored procedures | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Recompile stored procedures

Hi
I need to re-compile several stored procedures in a database. Is it possible to re-compile all at once. If so how do I do it? Thank you in advance.
Dipendra
Use sp_recompile [ @objname = ] ‘object’ To Recompile prcocedure. Write a script & use this Option. http://www.winnetmag.com/Article/ArticleID/15663/15663.html Thanks,
Sandy
Thanks for your reply sundeip but I need to re-compile all at once not one by one. Thanks
Dipendra
There is no such direct statement to recomplie all the stored procedures in the database.
YOu can schedule a job by listing the required stored procedures to recompile. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
For a quick and dirty solution you can do
SELECT
‘EXEC sp_recompile ‘ + name
FROM
sysobjects
WHERE
xtype=’P’
AND
OBJECTPROPERTY(id,’IsMsShipped’)=0 Paste the results back into Query Analyzer and execute it.
That should recompile all used-defined procedures and are not marked as system objects.
———————–
–Frank
http://www.insidesql.de
———————–

I know this is an old thread, but I wanted to say thanks to Frank… That little script was something I was searching for.
[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />] Glad it helped you out.<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
Well. To deal with spaces if any, here is modified code of Frank SELECT
‘EXEC sp_recompile [‘ + name +’]’
FROM
sysobjects
WHERE
xtype=’P’
AND
OBJECTPROPERTY(id,’IsMsShipped’)=0 Was code tag not supported earlier? Madhivanan Failing to plan is Planning to fail
It was, but somehow this newer version doesn’t recognize old postings as something to be parsed. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

It’s really strange. I just opened that above postings for editing and simply closed it again without changing a thing. Now it’s parsed. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

]]>