sp_recompile script/stored procedure

[sqldba20]

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 !!

[iburyak]

Change from this:

sp_recompile( @ObjName)

to this:

SET @Statement = 'sp_recompile ' + @ObjName
Exec (@Statement)

Good Luck.