SQL: TSQL to Find invalid views

we have a Tuesday/Thursday Maint window for one application we support. On Tuesdays we modify one facility and thursday if there are no errors we roll all the changes out to the other facilities. The application is 3ed party, and at times makes changes to the databases which render views invalid on our "reporting" server for the application.

Is there a way I can roll through all the views on my report server (There are about 70 of them) with TSQL to find the Binding Errors after Tuesdays Depolyment?

Errors I would like to find are:

Executed as user: <>. Statement(s) could not be prepared. [SQLSTATE 42000] (Error 8180) Could not use view or function 'PAT.dbo.Reorders' because of binding errors. [SQLSTATE 42000] (Error 4413) All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. [SQLSTATE 42000] (Error 205). The step failed.

Is there a way to do this programatically, or do I just have to keep right clicking each view and trying to see if it comes back with data?

Thanks,

Something like the following should be good enough to get you started. It should be quite lightweight as well.

DECLARE @Name       NVARCHAR( MAX ),
        @SQL        NVARCHAR( MAX );

DECLARE @t_BindingErrors TABLE
(
    ViewName        NVARCHAR( MAX ),
    ErrorMessage    NVARCHAR( MAX )
);

DECLARE c CURSOR 
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT  '[' + ss.name + '].[' + so.NAME + ']'
    FROM    sys.schemas ss
    INNER JOIN sys.objects so
        ON  ss.schema_id = so.schema_id
    WHERE   so.type = 'V';
OPEN    c;
FETCH NEXT FROM c 
INTO    @Name;

WHILE ( @@FETCH_STATUS = 0 )
BEGIN
    SET @SQL = '
        DECLARE @i  INTEGER;
        SELECT  TOP ( 0 ) @i = 1
        FROM    ' + @Name + ';';
    BEGIN TRY
        EXECUTE dbo.sp_executesql @statement = @SQL;
    END TRY BEGIN CATCH
        INSERT INTO @t_BindingErrors
        VALUES ( @Name, ERROR_MESSAGE() );
    END CATCH;  

    FETCH NEXT FROM c 
    INTO    @Name;
END;

CLOSE   c;
DEALLOCATE c;

SELECT  *
FROM    @t_BindingErrors;

StackExchange Database Administrators Q#82948, answer score: 12