CODEWITHSUNDEEP
sqlsql-server
Nikhil Khokale
Nikhil Khokale

Reputation: 25

How to find list of all views which gives error in specific database?

How to find list of all views or store procedures that gives error while executing in database?

sometime user made changes in views (deleting the specific field in view) without checking its dependencies. All the dependent views and store procedures using the views then gives error, XXX column is missing. Is there any specific script to check all the list of views and store procedures that gives error while executing.

Upvotes: 1

Views: 2472

Answers (1)

pmbAustin
pmbAustin

Reputation: 3970

This is the script I use to validate view metadata and refresh it to pick up any schema or view changes... this will probably work for you:

-- Refresh the metadata for all views in the 'dbo' schema
SET NOCOUNT ON
DECLARE @viewName AS VARCHAR(255)
DECLARE @count int

DECLARE listOfViews CURSOR FOR
    SELECT [TABLE_NAME]
      FROM INFORMATION_SCHEMA.VIEWS v
           LEFT OUTER JOIN sys.sql_dependencies d ON v.[TABLE_NAME] = OBJECT_NAME(d.object_id)
     WHERE [TABLE_SCHEMA] = 'dbo' AND
           (d.[class_desc] IS NULL OR d.[class_desc] <> 'OBJECT_OR_COLUMN_REFERENCE_SCHEMA_BOUND')
     GROUP BY [TABLE_NAME]
     ORDER BY [TABLE_NAME]

OPEN listOfViews
FETCH NEXT FROM listOfViews into @viewName

SET @count = 0
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    BEGIN TRY
        EXEC sp_refreshview @viewName
    END TRY
    BEGIN CATCH
        IF XACT_STATE() = -1 ROLLBACK
        PRINT @viewName + ' has ERRORS: ' + ERROR_MESSAGE()
        SET @count = @count + 1
    END CATCH
    FETCH NEXT FROM listOfViews INTO @viewName
END

CLOSE listOfViews
DEALLOCATE listOfViews

PRINT 'Total Views with errors: ' + CAST(@count AS nvarchar(10))

SET NOCOUNT OFF

Upvotes: 8

Related Questions