Broken View Finder

Shh, shhhhhh, we're being very very quiet, we're hunting broken views. Recently, we were asked to migrate some code changes and after doing so, the requesting team told us we had broken all of their views, but they couldn't tell us what was broken, just that everything was. After a quick rollback to snapshot, thank you Red Gate SQL Compare, I thought it'd be enlightening to see whether anything was broken before our code had been deployed.

You'll never guess what we discovered </clickbain&grt;

How can you tell a view is broken

The easiest way is SELECT TOP 1 * FROM dbo.MyView; but then you need to figure out all of your views.

That's easy enough, SELECT * FROM sys.schemas AS S INNER JOIN sys.views AS V ON V.schema_id = S.schema_id;

But you know, there's something built into SQL Server that will actually test your views - sys.sp_refreshview. That's much cleaner than running sys.sp_executesql with our SELECT TOP 1s

-- This script identifies broken views
-- and at least the first error with it
SET NOCOUNT ON;
DECLARE
    CSR CURSOR
FAST_FORWARD
FOR
SELECT
    CONCAT(QUOTENAME(S.name), '.', QUOTENAME(V.name)) AS vname
FROM
    sys.views AS V
    INNER JOIN
        sys.schemas AS S
        ON S.schema_id = V.schema_id;

DECLARE
    @viewname nvarchar(776);
DECLARE
    @BROKENVIEWS table
(
    viewname nvarchar(776)
,   ErrorMessage nvarchar(4000)
,   ErrorLine int
);

OPEN
    CSR;
FETCH
    NEXT FROM CSR INTO @viewname;

WHILE
    @@FETCH_STATUS = 0
BEGIN

    BEGIN TRY
        EXECUTE sys.sp_refreshview
            @viewname;
    END TRY
    BEGIN CATCH
        INSERT INTO @BROKENVIEWS(viewname, ErrorMessage, ErrorLine)
        VALUES
        (
            @viewname
        ,   ERROR_MESSAGE()
        ,   ERROR_LINE()
        );
        
    END CATCH

    FETCH
        NEXT FROM CSR INTO @viewname;
END

CLOSE CSR;
DEALLOCATE CSR;

SELECT
    B.*
FROM
    @BROKENVIEWS AS B