sp_recompile script/stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

sp_recompile script/stored procedure

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2007-10-18 : 16:35:36

Folks:

I want to run sp_recompile on a weekly basis. Does anybody have a script / stored procedure which will get all the Stored Procedures and Tables from a database and do a recompile. I wrote this small script but it is giving me a error as "Incorrect syntax near 'sp_recompile'."

CREATE PROCEDURE usp_Recompile
AS

DECLARE @ObjName varchar(255)
DECLARE @Statement nvarchar(255)
DECLARE @Command varchar(255)

DECLARE ObjCursor CURSOR FOR
SELECT name FROM sys.objects
where type in ('P','U')

OPEN ObjCursor
FETCH NEXT FROM ObjCursor INTO @ObjName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'RECOMPILING ' + @ObjName
sp_recompile (@ObjName)
--SET @Statement = 'UPDATE STATISTICS ' + @TableName + ' WITH FULLSCAN'
--EXEC sp_executesql @Statement
FETCH NEXT FROM ObjCursor INTO @ObjName
END
CLOSE ObjCursor
DEALLOCATE ObjCursor

Thanks !!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-18 : 16:47:32

shouldn't it be:
exec sp_recompile @ObjName
?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources