Not logged in - Login

SQL Refresh Stored Procedures and Functions

The following is an example of how to refresh stored procedures and functions.

DECLARE @sqlcmd NVARCHAR(MAX) = ''

SELECT --O.type_desc, SCHEMA_NAME(O.schema_id) + '.' + O.name, 1
       @sqlcmd = @sqlcmd +  'EXEC sys.sp_refreshsqlmodule ''' + SCHEMA_NAME(O.schema_id) + '.' + O.name + ''';
'
FROM sys.sql_modules M
INNER JOIN sys.objects O ON O.object_id = M.object_id
WHERE O.is_ms_shipped = 0 
        and O.type_desc NOT IN ('VIEW')
ORDER BY O.type_desc, O.name


print @sqlcmd

EXEC(@sqlcmd)