Início / dba / Perguntas / 284822
Accepted
T.S.
Asked: 2021-02-06 17:39:58 +0800 CST

Não pode confiar em INFORMATION_SCHEMA.view_definition?

  • 772

Eu construí um aplicativo que compara objetos nos bancos de dados e gera scripts para replicar objetos nos diferentes bancos de dados. O que notei enquanto estava usando isso para recuperar o código de visualização

select * from INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'V_SC_ALL_CUSTOMER_DOCUMENTS'

é que VIEW_DEFINITIONcorta a visão DDL curta. Então eu aprendi a usar sys.sql_modulespara obter definição completa.

eu corri esses

sp_help 'INFORMATION_SCHEMA.VIEWS'
sp_help 'sys.sql_modules'

e descobri que INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION nvarchartamanho = 8000 e sys.sql_modules.DEFINITION nvarchartamanho = -1. eu acredito nvarchar(max).

Qual é o ponto então ter VIEW_DEFINITIONse você não pode confiar nele? E existem outros tipos de objetos que eu deveria recuperar em sql_modulesvez de suas tabelas correspondentes, como procedimentos, gatilhos, etc?

sql-server ddl

2 respostas

  1. Best Answer

    Como Erik mencionou no artigo que ele vinculou, INFORMATION_SCHEMAé um esquema obsoleto dos dias clássicos do SQL Server 2000. Você deve ler o artigo que ele vinculou à medida que explica os motivos pelos quais está obsoleto e cita os documentos da Microsoft que suportam esses motivos.

    Em vez disso, você pode usar a seguinte consulta que aproveita a exibição sys.objects para obter a definição de uma exibição:

    SELECT m.[definition] AS ObjectDefinition
FROM sys.objects o
INNER JOIN sys.sql_modules m 
    ON m.[object_id] = o.[object_id]
WHERE o.[object_id] = OBJECT_ID('dbo.YourViewName')
    AND o.[type] = 'V';

    Ou a função OBJECT_DEFINITION()para também obter a definição da seguinte forma:

    SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.YourView')) AS ObjectDefinition;

    Uma ressalva para fazer isso de qualquer maneira é que ele conterá o último script usado para modificar o objeto. Portanto, se a View foi alterada desde sua criação, ela começará com a ALTER VIEWinstrução, caso contrário, ela terá a CREATE VIEWinstrução.

    • 3

  2. FWIW, eu trabalhei um pouco nas visualizações do esquema de informações nos dias. Entre outras coisas, criei um diagrama de banco de dados para eles (que provavelmente ainda tenho por aí, em algum lugar).

    Então veio 2005, e percebi como eles estavam fora de fase com o produto. Basta considerar o número de visualizações de catálogo (aproximadamente 280) com quantas visualizações de esquema de informações (21) temos. Além disso, antes de 2005, não tínhamos o conceito de esquema como temos hoje em dia, então, pelo que me lembro, "esquema" nessas visualizações poderia, em algum contexto, ser o nome do banco de dados. E é claro que isso não pode ser alterado devido a motivos de compatibilidade com versões anteriores. (Estou um pouco confuso com os detalhes, isso foi há uns 15 anos, afinal.)

    Para nós que estávamos mais envolvidos nisso naquela época, era bastante óbvio que a MS não gastava muitos recursos nisso, e eu, por um lado, deixei de usá-los como uma batata quente quando 2005 foi lançado.

    É uma boa ideia ter o padrão ANSI/ISO SQL para esse tipo de meta-dado. Mas ele simplesmente não voa na realidade, IMO.

    (O que me surpreendeu agora ao olhar é que encontrei um chamado SEQUENCES. Esse recurso foi introduzido em 2012, então aparentemente algum trabalho é feito para as visualizações info_schema, ainda ...)

    • 2

relate perguntas