Refresh All SQL Modules

gistfile1.sql

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

DECLARE ObjectCursor CURSOR FAST_FORWARD FOR
SELECT 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

OPEN ObjectCursor;

FETCH NEXT FROM ObjectCursor INTO @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
    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

    FETCH NEXT FROM ObjectCursor INTO @Name;
END

CLOSE ObjectCursor;
DEALLOCATE ObjectCursor;

Modified version from http://stackoverflow.com/questions/3027399/how-to-check-all-stored-procedure-is-ok-in-sql-server

I've created another one using temporary tables.
It's now running about 8 times faster.