Как найти список всех представлений, которые выдают ошибку в конкретной базе данных?

#sql-server #sql-server-2016

Вопрос:

Как найти список всех представлений или процедур хранения, которые выдают ошибку при выполнении в базе данных?

когда-то пользователь вносил изменения в представления (удаляя конкретное поле в представлении), не проверяя его зависимости. Затем все зависимые представления и процедуры хранения, использующие представления, выдают ошибку, столбец XXX отсутствует. Существует ли какой-либо конкретный скрипт для проверки всего списка представлений и процедур хранения, который выдает ошибку при выполнении.

1. Если у вас есть вложенные представления, я бы рекомендовал начать с этого списка и избавиться от них. Кажется логичным вложить представления, но в sql server вложенные представления очень серьезно повлияют на производительность.

2. Я бы посоветовал вам исследовать триггеры DDL ( msdn.microsoft.com/en-us/library/ms175941 (v=sql.110).aspx ). Это позволит вам отслеживать (и, возможно, предотвращать) изменения в представлениях.

3. Вы могли бы попробовать перенести базу данных в Visual Studio, а затем выполнить сборку на ее основе. Но я подозреваю, что если ваши пользователи нарушают просмотры, они, вероятно, нарушают еще кучу вещей.

4. Напишите скрипт, который просматривает мета-таблицы, чтобы получить список представлений, выбирает * из каждого представления и улавливает любую ошибку.

5. Вызов хранимой процедуры sp_refreshview для представления не только гарантирует, что кэшированные метаданные синхронизированы с любыми изменениями схемы, но и выведет соответствующую ошибку, если возникнут какие-либо проблемы с привязкой столбцов из-за изменения зависимостей. Смотрите Сценарий, размещенный Ниже, в качестве ответа:

Ответ №1:

Это скрипт, который я использую для проверки метаданных просмотра и обновления его, чтобы получить любую схему или просмотреть изменения… вероятно, это сработает для вас:

 -- 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