重新编译所有存储过程语法 SQL

---Together

DECLARE @sql VARCHAR(max)
 
SET @sql = '';
 
select @sql = @sql + 'exec sp_recompile ' + name+';' + CHAR(10)
from sys.objects
where type_desc in ('SQL_STORED_PROCEDURE')
      and is_ms_shipped = 0
 
EXEC(@sql)

------------------------------------------------------------------------------------------------

----Detail one by one

SELECT ROW_NUMBER() OVER(ORDER BY name) AS RID,'exec sp_recompile ''' + name +'''' AS Text
INTO #TEMP
FROM sys.objects WHERE TYPE = 'P'

DECLARE @MaxID INT
DECLARE @SQL VARCHAR(MAX)
SELECT @MaxID = MAX(RID) FROM #TEMP

WHILE (@MaxID IS NOT NULL)
BEGIN
	SELECT @SQL = Text
	FROM #TEMP
	WHERE RID = @MaxID
	
	PRINT @SQL
	EXEC (@SQL)
	
	SELECT @MaxID = MAX(RID) FROM #TEMP WHERE RID < @MaxID 
END