Início / dba / Perguntas / 343146
Accepted
Speedcat
Asked: 2024-10-22 10:29:52 +0800 CST

Como posso executar sp_refreshview em todas as visualizações no meu banco de dados SQL Server

  • 772

Estou usando o SQL Server e recebi um erro indicando que uma ou mais das minhas exibições SQL estão fora de sincronia com a tabela SQL subjacente.

'MySQLServerName' retornou dados que não correspondem ao comprimento de dados esperado para a coluna 'MyColumnName'. O comprimento de dados esperado (máximo) é 50, enquanto o comprimento de dados retornado é 52.

Isso ocorre se você atualizar a definição da tabela SQL subjacente, mas esquecer de atualizar a(s) visualização(ões) SQL associada(s). Uma correção rápida para esse erro é executar sp_refreshview :

use MySQLDatabaseName
go
 
EXECUTE sp_refreshview N'MyViewName';

Mas e se eu tiver uma longa lista de visualizações quebradas, ou nem mesmo souber quais visualizações estão quebradas, ou não souber quais definições de tabela subjacentes não correspondem mais a essas visualizações? E se eu quiser economizar tempo executando sp_refreshview em todas as visualizações do meu banco de dados. Qual é uma maneira fácil de realizar essa tarefa?

sql-server

2 respostas

  1. Best Answer

    Chegarei à minha solução em um minuto.

    Mas, antes de tudo, podemos evitar esse problema completamente . Gostaria de tirar o chapéu para Aaron Bertrand , que nos ajudou a entender o benefício de usar WITH SCHEMABINDING ao criar uma exibição do SQL Server. De Aaron:

    Você pode usar WITH SCHEMABINDING nas visualizações, o que imporá explicitamente que você não pode modificar objetos referenciados sem remover conscientemente a associação de esquema. (Como bônus adicional, suas visualizações não podem usar SELECT *, o que provavelmente é parte do problema aqui).

    Escrevi sobre isso no seguinte artigo:

    Benefícios do SCHEMABINDING

    Para responder à pergunta original , você pode facilmente executar sp_refreshview em todas as visualizações do seu banco de dados simplesmente executando a seguinte instrução:

    USE [MyDatabaseName]
GO

EXECUTE sp_MSforeachtable 
@command1 = 'sp_refreshview [?];', 
@whereand = 'and OBJECTPROPERTY(o.id, N''IsUserTable'') = 0 
or OBJECTPROPERTY(o.id, N''IsView'') = 1'

    Aqui estão algumas informações sobre sp_MSforeachtable e como você pode estendê-lo para iterar por diferentes objetos do SQL Server. Observe que sp_MSforeachtable é um procedimento armazenado do sistema não documentado, então ele pode mudar a qualquer momento. Até agora eu o testei com sucesso no SQL Server 2019.

    Boa manutenção do SQL Server para todos!

    • 5

  2. Parece desperdício atualizar todas as visualizações. Você só precisa atualizar as visualizações que acessam essa tabela. Além disso, você precisa atualizar procedimentos, gatilhos e funções, o que você pode fazer com sp_refreshsqlmodule(o que também abrange visualizações).

    Você pode usar o seguinte script para automatizá-lo. Ele será usado sys.sql_expression_dependenciespara obter todas as referências à tabela relevante e atualizar esses módulos.

    DECLARE @yourTable nvarchar(1000) = N'dbo.t';  -- CHANGE THIS LINE

DECLARE @sql nvarchar(max) = (
    SELECT STRING_AGG(N'
EXEC sp_refreshsqlmodule N''' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ''';',
      '')
    FROM sys.objects o
    JOIN sys.schemas s ON s.schema_id = o.schema_id
    WHERE o.object_id IN (
        SELECT ed.referencing_id
        FROM sys.sql_expression_dependencies ed
        WHERE ed.referenced_id = OBJECT_ID(@yourTable)
    )
      AND o.type IN ('P', 'V', 'FN', 'IF', 'TF', 'TR')
);

PRINT @sql;

EXEC sp_executesql @sql;
    • 4

relate perguntas