Refresh all SQL Views and Stored Procedures

 
declare @vwTbl table (viewName varchar(356), ErrorNumber bigint, ErrorSeverity bigint, ErrorState varchar(100),ErrorProcedure varchar(100), ErrorLine bigint, ErrorMessage varchar(800))

--REFRESHING VIEWS:
DECLARE @ViewName VARCHAR(256)
DECLARE cViews CURSOR READ_ONLY FOR SELECT name from sys.views
OPEN cViews
FETCH NEXT FROM cViews INTO @ViewName
WHILE @@FETCH_STATUS != -1
BEGIN
	BEGIN TRY		
		EXEC SP_REFRESHVIEW @ViewName
		PRINT 'View ''' + @ViewName + ''' has been refreshed.'
	END TRY
	BEGIN CATCH
		BEGIN
			insert into @vwTbl
			SELECT
			@ViewName as ViewName
			,ERROR_NUMBER() AS ErrorNumber
			,ERROR_SEVERITY() AS ErrorSeverity
			,ERROR_STATE() AS ErrorState
			,ERROR_PROCEDURE() AS ErrorProcedure
			,ERROR_LINE() AS ErrorLine
			,ERROR_MESSAGE() AS ErrorMessage;
		END
	END CATCH
	FETCH NEXT FROM cViews INTO @ViewName
END
CLOSE cViews
DEALLOCATE cViews

select * from @vwTbl

GO

--REFRESHING OTHER OBJECTS:
SET NOCOUNT ON;

DECLARE
        @RowIndex AS SMALLINT = 1
        , @ObjectName AS NVARCHAR(128)
        , @Sql AS NVARCHAR(MAX)
        

-- Get list of objects (stored procedures, functions) to be refreshed
DECLARE @ObjectsTable AS Table (RowIndex SMALLINT IDENTITY(1,1), ObjectType nvarchar(60), ObjectName NVARCHAR(128), IsRefreshed BIT, Error NVARCHAR(MAX))
INSERT INTO @ObjectsTable (ObjectType, ObjectName, IsRefreshed)
SELECT O.type_desc, SCHEMA_NAME(O.schema_id) + '.' + O.name, 1
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

-- For each function/sp...
WHILE @RowIndex <= (SELECT COUNT(*) FROM @ObjectsTable)
BEGIN   
        SELECT @ObjectName = ObjectName         
        FROM @ObjectsTable
        WHERE RowIndex = @RowIndex
        
        SET @Sql = 'EXEC sp_refreshsqlmodule ''' + @ObjectName + ''''
        BEGIN TRY
                EXEC sp_executesql @Sql
        END TRY
        BEGIN CATCH
                UPDATE @ObjectsTable
                SET
                        IsRefreshed = 0, 
                        Error = ERROR_MESSAGE()
                WHERE RowIndex = @RowIndex
                
                IF @@TRANCOUNT > 0
                        ROLLBACK TRAN
        END CATCH
                                                                
        -- Process next object
        SET @RowIndex = @RowIndex + 1
END


-- List all objects not refreshed
SELECT ObjectType, ObjectName, IsRefreshed, Error
FROM @ObjectsTable
WHERE IsRefreshed = 0
GO