MS SQL: sp_recompile all procs

--// Forgo the typical looping with TADA

DECLARE @X VarChar(Max)
SELECT @X =
(
SELECT
N'exec sp_recompile ' + '[' + o.[Name] + ']; ' + CHAR(10)
FROM
dbo.sysobjects o
INNER JOIN dbo.sysusers u ON o.uid = u.uid
WHERE
o.Type = 'p'
AND o.[Name] NOT LIKE 'dt_%'
FOR XML PATH(''));
PRINT @X
--//EXEC(@X)


--// Eh, just hope the text isn't greater than 8000 characters.
--// If you're number of procs is huge, well loop or "results to text" the following
--// and then execute results.

SELECT
'exec sp_recompile ' + '[' + o.[Name] + '];'
FROM
dbo.sysobjects o
INNER JOIN dbo.sysusers u ON o.uid = u.uid
WHERE
o.Type = 'p'
AND o.[Name] NOT LIKE 'dt_%'

Comments :

0 comments to “MS SQL: sp_recompile all procs”