Refresh All SQL Modules

gistfile1.sql

DECLARE @Name nvarchar(1000);
DECLARE @Sql nvarchar(1000);
DECLARE @Result int;

IF OBJECT_ID('tempdb..#myTable') IS NOT NULL
DROP TABLE #myTable

CREATE TABLE #myTable(
row INT,
query NVARCHAR(500))


INSERT INTO #myTable
SELECT ROW_NUMBER() OVER (ORDER BY o.object_id) AS Row,SCHEMA_NAME(o.schema_id) + '.[' + OBJECT_NAME(o.object_id) + ']'
FROM sys.objects o
WHERE type_desc IN (
'SQL_STORED_PROCEDURE',
'SQL_TRIGGER',
'SQL_SCALAR_FUNCTION',
'SQL_TABLE_VALUED_FUNCTION',
'SQL_INLINE_TABLE_VALUED_FUNCTION',
'VIEW')
and isnull(objectproperty(o.object_id,'IsSchemaBound'),0)=0	; -- don't check schemabinding they don't like it

DECLARE @row INT =0 ;

WHILE @row < @@ROWCOUNT
BEGIN
	SELECT @name = query FROM  #myTable WHERE row = @row
    SET @Sql = N'EXEC sp_refreshsqlmodule ''' + @Name + '''';
    PRINT @Sql;

    BEGIN TRY
        EXEC @Result = sp_executesql @Sql;
        IF @Result <> 0 RAISERROR('Failed', 16, 1);
    END TRY
    BEGIN CATCH
		PRINT @Sql
        PRINT 'The module ''' + @Name + ''' does not compile.';
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    END CATCH
end