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. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

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

]]>