SP_COLUMNS

Boa tarde pessoal.

Vi uma pergunta em um grupo de SQL Server e lembrei dessa dica.

A pergunta era: Como encontrar as tabelas que possuem uma determinada coluna?

A resposta parece simples, e de fato ela é, a maioria vai pela query tradicional:

–Query para listar todas as tabelas que possuem a coluna chamada ‘APELIDO’ SELECT * FROM SYSOBJECTS O INNER JOIN SYSCOLUMNS C ON O.id = C.id WHERE O.xtype = ‘U’ AND C.name = ‘APELIDO’

A junção da ‘sysobjects’ com a ‘syscolumns’ onde ‘xtype = ‘U” filtra apenas tabelas de usuários, um consulta bem simples de se fazer.

Porém conseguimos atingir o mesmo resultado também com uma procedure nativa do SQL Server chamada ‘SP_COLUMNS’ conforme exemplo abaixo:

— Filtra todas as tabelas que possuem a coluna ‘Apelido’ SP_COLUMNS @table_name = ‘%%’,@column_name = ‘APELIDO’

Podemos ver que os Result sets são diferentes, no primeiro traz algumas informações sobre o objeto (tabela) e sobre a coluna, no segundo as informações são apenas sobre a coluna como o datatype,  precisão, tamanho, posição, se permite null… Porém se o que te interessa é saber apenas quais tabelas possuem aquela coluna já atende sua necessidade.

Podemos também filtrar a coluna como se fosse um LIKE ‘%%’, por exemplo quero todas as tabelas que possuem alguma coluna que comece com ‘ID_’.

Nesse caso especifiquei o ‘@table_owner’ para não trazer informações sobre os metadados de sistema.

Se você prestou atenção deve estar se perguntando porque da coluna ‘IDPLAN’ esta sendo exibida ?!  A resposta para isso:

Expressões regulares, esse carácter(_) representa ‘qualquer coisa’ ou ‘qualquer carácter ‘.

O correto então seria assim:

SP_COLUMNS @table_name = ‘%%’,@table_owner = ‘dbo’,@column_name = ‘ID[_]%’

Bom podemos também fazer o contrário, podemos pelo nome da tabela saber quais colunas pertencem a ela.

SP_COLUMNS @table_name = ‘TB_MESAS’,@table_owner = ‘dbo’

Conseguimos ver facilmente as colunas dessa tabela e temos praticamente toda as informações sobre essas colunas.

Posso pegar todas as tabelas que começam com um nome especifico e todas as suas colunas, esse result set é bem rico em informações sobre as colunas.

Bom galera era essa a dica, utilizo muito no dia-a-dia essa procedure é bem fácil e rápido de pesquisar com ela, o SQL Server tem varias outras procedures como essa, vou tentar falar mais sobre elas em outros posts, até a próxima.

Quem quiser saber mais detalhes sobre a SP_COLUMNS abaixo o link do BOL.

Reginaldo Silva

Av. Cassiano Ricardo, 319 | Sala 1702 - 1703 | Jd. Aquarius
CEP: 12.246-870 | São José dos Campos - SP