Como retornar todas colunas participantes de uma chave primária ?

Boa Noite Pessoal,

Para saldar a média de uma postagem por semana, hoje colocarei uma dica rápida. Essa semana uma das administradoras de dados (AD) em um dos clientes em que trabalho me fez uma pergunta simples “Qual é o comando para retornar as colunas que compõe uma chave primária ?”. De fato é uma pergunta bem simples e quase sempre é respondida de modo visual através do Enterprise Manager ou do SQL Server Management Studio. Mas e via T-SQL ? Como fazer ?

A forma mais fácil de saber qual a coluna (ou combinação de colunas) que representa a chave primária é através da stored procedure sp_pkeys. Ex:

— Muda o contexto para o Adventure Works
USE AdventureWorks

— Lista as colunas da chave primária de Sales.SalesOrderHeader
EXEC sp_pkeys ‘SalesOrderDetail’, ‘Sales’

Normalmente essa é a maneira preferida (acredito até que a AD já conhecia), mas o fato do retorno ser através de uma stored procedure pode trazer alguns inconvenientes como a impossibilidade de retornar todas as colunas que são chaves primárias de todas as tabelas. Uma segunda opção seria utilizar a view Key_Column_Usage do schema Information_Schema.

— Muda o contexto para o Adventure Works
USE AdventureWorks

— Retorna os dados de INFORMATION_SCHEMA.KEY_COLUMN_USAGE
— Apenas as colunas de Sales.SalesOrderDetails

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = ‘Sales’ AND TABLE_NAME = ‘SalesOrderDetail’

Essa abordagem é interessante, mas o problema com a view Key_Column_Usage é que ela retorna também os dados de colunas envolvidas em unique constraints e foreign_keys. Alguns ajustes podem ser feitos para retornar apenas as colunas envolvidas em Primary Keys.

— Muda o contexto para o Adventure Works
USE AdventureWorks

— Retorna os dados de INFORMATION_SCHEMA.KEY_COLUMN_USAGE
— Apenas as colunas de Sales.SalesOrderDetails

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE As K
WHERE TABLE_SCHEMA = ‘Sales’ AND TABLE_NAME = ‘SalesOrderDetail’
AND CONSTRAINT_NAME = (
    SELECT NAME FROM SYSOBJECTS As U
    WHERE
        K.TABLE_NAME = OBJECT_NAME(U.Parent_Obj) AND
        U.XTYPE = ‘PK’)
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

Basta apenas retirar o predicado TABLE_SCHEMA e TABLE_NAME para ter uma lista com todas as colunas com chave primária do banco inteiro. Utilizei tabelas como SYSOBJECTS apenas para manter a compatibilidade com o SQL Server 2000 (era a plataforma que a AD estava utilizando). Para plataformas como o 2005 e o 2008 a solução pode utilizar as novas views de catálogo como a Sys.Indexes, Sys.Index_Columns e Sys.Columns ou combinar a Sys.Indexes com a Information_Schema.Key_Column_Usage. Ex:

— Método 1
SELECT
    OBJECT_SCHEMA_NAME(I.object_id) As Esquema,
    OBJECT_NAME(I.object_id) As Tabela,
    C.Name As Nome, IC.key_ordinal As Posicao
FROM
    sys.indexes As I
    INNER JOIN sys.index_columns As IC ON I.index_id = IC.index_id AND I.object_id = IC.object_id
    INNER JOIN sys.columns As C ON IC.column_id = C.column_id AND IC.object_id = C.object_id
WHERE
    I.is_primary_key = 1 AND OBJECTPROPERTYEX(I.object_id,‘IsUserTable’) = 1
ORDER BY Esquema, Tabela, Posicao

— Método 2
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE As K
WHERE CONSTRAINT_NAME = (
    SELECT FROM FROM sys.indexes As I
    WHERE
        K.TABLE_NAME = OBJECT_NAME(I.object_id) AND
        K.TABLE_SCHEMA = OBJECT_SCHEMA_NAME(I.object_id) AND
        I.is_primary_key = 1)
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

Aqueles que desejarem utilizar a construção do 2000 nas versões posteriores pode ser necessário fazer alguns ajustes por conta do schema caso haja mais uma mesma tabela em esquemas diferentes.

[ ]s,

Gustavo

Deixe uma resposta

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