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’

screen-shot-12-29-16-at-01-24-pm

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’

screen-shot-12-29-16-at-01-33-pm

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_’.

screen-shot-12-29-16-at-02-00-pm

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

https://msdn.microsoft.com/pt-br/library/ms174214.aspx

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’

screen-shot-12-29-16-at-02-07-pm

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

 

screen-shot-12-29-16-at-02-11-pm

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.

https://msdn.microsoft.com/en-us/library/ms176077.aspx

Reginaldo Silva

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s