Scripts e tabelas de catálogo 03: encontrando colunas no SQL Server

Apresentei nos artigos anteriores (aqui e aqui) exemplos de como usar as tabelas de catálogo do DB2 para criar scripts com consultas trabalhosas e consultas sobre estrutura de dados. Nesta semana, eu apresento um novo exemplo, desta vez para o SQL↳SQL64 conteúdosSQL Server – Como evitar SQL Injection?Data · mai 2019Azure SQL DB Managed InstanceData · abr 2019SQL Server – Como evitar SQL Injection? Pare de utilizar Query Dinâmica como EXEC(@Query)Data · abr 2019Ver tudo em Data → Server 2012.

No SQL Server, essas tabelas são chamadas de tabelas de sistema. Como você deve saber, cada banco de dados tem várias tabelas de sistema e cada instância do SQL Server possui quatro bancos de dados de sistema (MASTER, MODEL, MSDB, TEMP), que por sua vez trazem metadados sobre a configuração da instância e alguns serviços, como os jobs.

Muito tempo atrás, o DBA precisava conhecer um monte de tabelas de sistema para obter informações de ambiente. Mas, desde o SQL Server 2005, existem as Dynamic Management Views (DMVs), que facilitam muito esse trabalho. Seria mais lógico se a palavra “views” fosse substituída por “objects”, uma vez que existem visões, funções escalares e funções tabulares que oferecem essas informações. Porém a sigla DMO já é usada para outros fins (“Distributed Management Objects”), por isso usamos DMV como uma designação genérica.

O exemplo que mostro aqui decorreu de uma necessidade prática de um projeto do qual participei. O cliente possuía um banco de dados cujo modelo incluía centenas de tabelas…. e não havia documentação adequada. Para piorar as coisas, o arquiteto do banco não fazia mais parte do quadro de funcionários.

Precisávamos atualizar dados de um determinado campo, mas ninguém sabia em qual ou quais tabelas iríamos encontrar o referido campo. O problema é que o modelo do banco de dados era muito grande, o que complicava bastante a situação. É a treva total, mas lamentavelmente é um situação até comum nos projetos.

Portanto, eu tinha basicamente duas alternativas. Ou saía pesquisando tabela por tabela para encontrar o campo desejado, ou então inventava um método mais inteligente. Eu optei pela segunda alternativa e criei um script usando as DMVs, como descreverei a seguir.

No antigo SQL Server 2000, as tabelas de sistema que continham dados sobre todas as tabelas e colunas do banco eram sysobjects e syscolumns. Elas ainda existem no SQL Server 2012 como DMVs, mas apenas por uma questão de compatibilidade retroativa. Sendo assim, é melhor usar as versões atuais destas DMVs, que são :

• sys.all_objects
• sys.all_columns

Para ilustrar o problema, eu usei a base de dados demo AdventureWorks2012. O script a seguir pesquisa todas as tabelas de usuário (type=’U’)  para informar em quais delas existe um campo chamado ‘ProductID’. Acrescento à listagem final o tipo de dados e tamanho máximo de cada campo, obtidos da DMV sys.types. Juntando todos esses critérios, tem-se a consulta a seguir.

SELECT

                 O.NAME AS TABELA,

                 C.NAME AS COLUNA,

                 T.NAME AS TIPODADOS,

                 C.MAX_LENGTH AS TAMANHO

            FROM SYS.ALL_OBJECTS O

                 INNER JOIN SYS.ALL_COLUMNS C ON O.OBJECT_ID=C.OBJECT_ID

                 INNER JOIN SYS.TYPES T ON C.USER_TYPE_ID=T.USER_TYPE_ID

            WHERE O.TYPE='U'

                 AND C.NAME = 'ProductID'

            ORDER BY 1,2

–resultado

tabela                                      coluna            tipodados         tamanho

————————————– ——————- —————— —————-

Product                                  ProductID       int                                4

ProductCostHistory               ProductID       int                                4

ProductDocument                  ProductID       int                                4

ProductInventory                   ProductID       int                                4

ProductListPriceHistory        ProductID       int                                4

ProductProductPhoto            ProductID       int                                4

ProductReview                      ProductID       int                                4

ProductVendor                       ProductID       int                                4

PurchaseOrderDetail             ProductID       int                                4

SalesOrderDetail                   ProductID       int                                4

ShoppingCartItem                  ProductID       int                                4

SpecialOfferProduct              ProductID       int                                4

TransactionHistory                ProductID       int                                4

TransactionHistoryArchive    ProductID       int                                4

WorkOrder                             ProductID       int                                4

WorkOrderRouting                ProductID       int                                4

(16 row(s) affected)

O leitor mais atento vai notar que este script pode ser usado de várias outras formas. Pode-se usá-lo, por exemplo, para identificar quais colunas usam um determinado tipo de dados ou que tenham um certo tamanho máximo. As adaptações necessárias são elementares e eu deixo esse trabalho a cargo do leitor interessado nesses cenários.

Por hoje é só. Até a próxima!