Como você lista a chave primária de uma tabela do SQL Server?

Pergunta simples, como você lista a chave primária de uma tabela com o T-SQL? Eu sei como obter índices em uma tabela, mas não consigo lembrar como obter o PK.

SELECT Col.Column_Name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col WHERE Col.Constraint_Name = Tab.Constraint_Name AND Col.Table_Name = Tab.Table_Name AND Constraint_Type = 'PRIMARY KEY' AND Col.Table_Name = '' 

Geralmente, é recomendável praticar agora o uso das exibições sys.* Sobre INFORMATION_SCHEMA no SQL Server, portanto, a menos que você esteja planejando migrar bancos de dados, eu as utilizaria. Veja como você faria isso com as exibições sys.* :

 SELECT c.name AS column_name, i.name AS index_name, c.is_identity FROM sys.indexes i inner join sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id inner join sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id WHERE i.is_primary_key = 1 and i.object_ID = OBJECT_ID('.'); 

Esta é uma solução que usa apenas tabelas sys .

Ele lista todas as chaves primárias no database. Ele retorna o esquema, o nome da tabela, o nome da coluna e a ordem de sorting da coluna correta para cada chave primária.

Se você deseja obter a chave primária de uma tabela específica, precisará filtrar SchemaName e TableName .

IMHO, esta solução é muito genérica e não usa literais de string, por isso vai rodar em qualquer máquina.

 select s.name as SchemaName, t.name as TableName, tc.name as ColumnName, ic.key_ordinal as KeyOrderNr from sys.schemas s inner join sys.tables t on s.schema_id=t.schema_id inner join sys.indexes i on t.object_id=i.object_id inner join sys.index_columns ic on i.object_id=ic.object_id and i.index_id=ic.index_id inner join sys.columns tc on ic.object_id=tc.object_id and ic.column_id=tc.column_id where i.is_primary_key=1 order by t.name, ic.key_ordinal ; 

Aqui está outra maneira da pergunta obter a chave primária da tabela usando a consulta sql :

 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA+'.'+CONSTRAINT_NAME), 'IsPrimaryKey') = 1 AND TABLE_NAME = '  ' 

Ele usa KEY_COLUMN_USAGE para determinar as restrições para uma determinada tabela
Em seguida, usa OBJECTPROPERTY( id , 'IsPrimaryKey') para determinar se cada um é uma chave primária

Está usando o MS SQL Server você pode fazer o seguinte:

 --List all tables primary keys select * from information_schema.table_constraints where constraint_type = 'Primary Key' 

Você também pode filtrar na coluna table_name se quiser uma tabela específica.

Eu gosto da técnica INFORMATION_SCHEMA, mas outra que eu usei é: exec sp_pkeys ‘table’

– Esta é outra versão modificada, que também é um exemplo de consulta relacionada

 SELECT TC.TABLE_NAME as [Table_name], TC.CONSTRAINT_NAME as [Primary_Key] FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND TC.TABLE_NAME IN (SELECT [NAME] AS [TABLE_NAME] FROM SYS.OBJECTS WHERE TYPE = 'U') 

Isso deve listar todas as restrições (Chave primária e Chaves estrangeiras) e no final do nome da tabela de colocação da consulta

 /* CAST IS DONE , SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT*/ WITH ALL_KEYS_IN_TABLE (CONSTRAINT_NAME,CONSTRAINT_TYPE,PARENT_TABLE_NAME,PARENT_COL_NAME,PARENT_COL_NAME_DATA_TYPE,REFERENCE_TABLE_NAME,REFERENCE_COL_NAME) AS ( SELECT CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)) , CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)) , PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)) , PARENT_COL_NAME=CAST ( PKnUKEYCol.name AS VARCHAR(30)) , PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE, REFERENCE_TABLE_NAME='' , REFERENCE_COL_NAME='' FROM sys.key_constraints as PKnUKEY INNER JOIN sys.tables as PKnUTable ON PKnUTable.object_id = PKnUKEY.parent_object_id INNER JOIN sys.index_columns as PKnUColIdx ON PKnUColIdx.object_id = PKnUTable.object_id AND PKnUColIdx.index_id = PKnUKEY.unique_index_id INNER JOIN sys.columns as PKnUKEYCol ON PKnUKEYCol.object_id = PKnUTable.object_id AND PKnUKEYCol.column_id = PKnUColIdx.column_id INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl ON oParentColDtl.TABLE_NAME=PKnUTable.name AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name UNION ALL SELECT CONSTRAINT_NAME= CAST (oConstraint.name AS VARCHAR(30)) , CONSTRAINT_TYPE='FK', PARENT_TABLE_NAME=CAST (oParent.name AS VARCHAR(30)) , PARENT_COL_NAME=CAST ( oParentCol.name AS VARCHAR(30)) , PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE, REFERENCE_TABLE_NAME=CAST ( oReference.name AS VARCHAR(30)) , REFERENCE_COL_NAME=CAST (oReferenceCol.name AS VARCHAR(30)) FROM sys.foreign_key_columns FKC INNER JOIN sys.sysobjects oConstraint ON FKC.constraint_object_id=oConstraint.id INNER JOIN sys.sysobjects oParent ON FKC.parent_object_id=oParent.id INNER JOIN sys.all_columns oParentCol ON FKC.parent_object_id=oParentCol.object_id /* ID of the object to which this column belongs.*/ AND FKC.parent_column_id=oParentCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/ INNER JOIN sys.sysobjects oReference ON FKC.referenced_object_id=oReference.id INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl ON oParentColDtl.TABLE_NAME=oParent.name AND oParentColDtl.COLUMN_NAME=oParentCol.name INNER JOIN sys.all_columns oReferenceCol ON FKC.referenced_object_id=oReferenceCol.object_id /* ID of the object to which this column belongs.*/ AND FKC.referenced_column_id=oReferenceCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/ ) select * from ALL_KEYS_IN_TABLE where PARENT_TABLE_NAME in ('YOUR_TABLE_NAME') or REFERENCE_TABLE_NAME in ('YOUR_TABLE_NAME') ORDER BY PARENT_TABLE_NAME,CONSTRAINT_NAME; 

Para referência, leia por favor – http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx

Obrigado cara.

Com uma ligeira variação eu usei para encontrar todas as chaves primárias para todas as tabelas.

 SELECT A.Name,Col.Column_Name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col , (select NAME from dbo.sysobjects where xtype='u') AS A WHERE Col.Constraint_Name = Tab.Constraint_Name AND Col.Table_Name = Tab.Table_Name AND Constraint_Type = 'PRIMARY KEY ' AND Col.Table_Name = A.Name 
 SELECT A.TABLE_NAME as [Table_name], A.CONSTRAINT_NAME as [Primary_Key] FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME 

Este te dá as colunas que são PK.

 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'TableName' 

Eu achei isso útil, dá uma lista de tabelas com uma lista separada por vírgulas das colunas e, em seguida, também uma lista separada por vírgulas de quais são a chave primária

 SELECT T.TABLE_SCHEMA, T.TABLE_NAME, STUFF(( SELECT ', ' + C.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS C WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA AND T.TABLE_NAME = C.TABLE_NAME FOR XML PATH ('') ), 1, 2, '') AS Columns, STUFF(( SELECT ', ' + C.COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON C.TABLE_SCHEMA = TC.TABLE_SCHEMA AND C.TABLE_NAME = TC.TABLE_NAME WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA AND T.TABLE_NAME = C.TABLE_NAME AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY' FOR XML PATH ('') ), 1, 2, '') AS [Key] FROM INFORMATION_SCHEMA.TABLES T ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME 

Abaixo da consulta irá listar as chaves primárias da tabela particular :

 SELECT DISTINCT CONSTRAINT_NAME AS [Constraint], TABLE_SCHEMA AS [Schema], TABLE_NAME AS TableName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'mytablename' 

Eu estou dizendo uma técnica simples que eu sigo

 SP_HELP 'table_name' 

execute este código como consulta. Mencione o nome da sua tabela no lugar de table_name para o qual você deseja conhecer a chave primária (não esqueça as aspas simples). O resultado será mostrado como imagem anexada. Espero que isso ajude você

insira a descrição da imagem aqui

 SELECT t.name AS 'table', i.name AS 'index', it.xtype, (SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k ON k.indid = i.indid AND c.colid = k.colid AND c.id = t.id AND k.keyno = 1 AND k.id = t.id) AS 'column1', (SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k ON k.indid = i.indid AND c.colid = k.colid AND c.id = t.id AND k.keyno = 2 AND k.id = t.id) AS 'column2', (SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k ON k.indid = i.indid AND c.colid = k.colid AND c.id = t.id AND k.keyno = 3 AND k.id = t.id) AS 'column3', (SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k ON k.indid = i.indid AND c.colid = k.colid AND c.id = t.id AND k.keyno = 4 AND k.id = t.id) AS 'column4', (SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k ON k.indid = i.indid AND c.colid = k.colid AND c.id = t.id AND k.keyno = 5 AND k.id = t.id) AS 'column5', (SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k ON k.indid = i.indid AND c.colid = k.colid AND c.id = t.id AND k.keyno = 6 AND k.id = t.id) AS 'column6', (SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k ON k.indid = i.indid AND c.colid = k.colid AND c.id = t.id AND k.keyno = 7 AND k.id = t.id) AS 'column7', (SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k ON k.indid = i.indid AND c.colid = k.colid AND c.id = t.id AND k.keyno = 8 AND k.id = t.id) AS 'column8', (SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k ON k.indid = i.indid AND c.colid = k.colid AND c.id = t.id AND k.keyno = 9 AND k.id = t.id) AS 'column9', (SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k ON k.indid = i.indid AND c.colid = k.colid AND c.id = t.id AND k.keyno = 10 AND k.id = t.id) AS 'column10', FROM sysobjects t INNER JOIN sysindexes i ON i.id = t.id INNER JOIN sysobjects it ON it.parent_obj = t.id AND it.name = i.name WHERE it.xtype = 'PK' ORDER BY t.name, i.name 

O procedimento armazenado do sistema sp_help fornecerá as informações. Execute a seguinte declaração:

 execute sp_help table_name 

Experimente:

 SELECT CONSTRAINT_CATALOG AS DataBaseName, CONSTRAINT_SCHEMA AS SchemaName, TABLE_NAME AS TableName, CONSTRAINT_Name AS PrimaryKey FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'Primary Key' and Table_Name = 'YourTable' 

Esta versão exibe o esquema, o nome da tabela e uma lista ordenada e separada por vírgula de chaves primárias. Object_Id () não funciona para servidores de link, portanto filtramos pelo nome da tabela.

Sem o REPLACE (Si1.Column_Name, ”, ”), ele mostraria as tags xml de abertura e fechamento de Column_Name no database que eu estava testando. Não sei por que o database exigiu uma substituição para ‘Column_Name’, portanto, se alguém souber, por favor, comente.

 DECLARE @TableName VARCHAR(100) = ''; WITH Sysinfo AS (SELECT Kcu.Table_Name , Kcu.Table_Schema AS Schema_Name , Kcu.Column_Name , Kcu.Ordinal_Position FROM [LinkServer].Information_Schema.Key_Column_Usage Kcu JOIN [LinkServer].Information_Schema.Table_Constraints AS Tc ON Tc.Constraint_Name = Kcu.Constraint_Name WHERE Tc.Constraint_Type = 'Primary Key') SELECT Schema_Name ,Table_Name , STUFF( ( SELECT ', ' , REPLACE(Si1.Column_Name, '', '') FROM Sysinfo Si1 WHERE Si1.Table_Name = Si2.Table_Name ORDER BY Si1.Table_Name , Si1.Ordinal_Position FOR XML PATH('') ), 1, 2, '') AS Primary_Keys FROM Sysinfo Si2 WHERE Table_Name = CASE WHEN @TableName NOT IN( '', 'All') THEN @TableName ELSE Table_Name END GROUP BY Si2.Table_Name, Si2.Schema_Name; 

E o mesmo padrão usando a consulta de George:

 DECLARE @TableName VARCHAR(100) = ''; WITH Sysinfo AS (SELECT S.Name AS Schema_Name , T.Name AS Table_Name , Tc.Name AS Column_Name , Ic.Key_Ordinal AS Ordinal_Position FROM [LinkServer].Sys.Schemas S JOIN [LinkServer].Sys.Tables T ON S.Schema_Id = T.Schema_Id JOIN [LinkServer].Sys.Indexes I ON T.Object_Id = I.Object_Id JOIN [LinkServer].Sys.Index_Columns Ic ON I.Object_Id = Ic.Object_Id AND I.Index_Id = Ic.Index_Id JOIN [LinkServer].Sys.Columns Tc ON Ic.Object_Id = Tc.Object_Id AND Ic.Column_Id = Tc.Column_Id WHERE I.Is_Primary_Key = 1) SELECT Schema_Name ,Table_Name , STUFF( ( SELECT ', ' , REPLACE(Si1.Column_Name, '', '') FROM Sysinfo Si1 WHERE Si1.Table_Name = Si2.Table_Name ORDER BY Si1.Table_Name , Si1.Ordinal_Position FOR XML PATH('') ), 1, 2, '') AS Primary_Keys FROM Sysinfo Si2 WHERE Table_Name = CASE WHEN @TableName NOT IN('', 'All') THEN @TableName ELSE Table_Name END GROUP BY Si2.Table_Name, Si2.Schema_Name; 

Tabela Sys.Objects contém linha para cada object com escopo de esquema definido pelo usuário.

Restrições criadas como Chave Primária ou outras serão o object e o nome da Tabela será o object pai

Consultar sys.Objects e coletar os IDs do object do tipo necessário

 declare @TableName nvarchar(50)='TblInvoice' -- your table name declare @TypeOfKey nvarchar(50)='PK' -- For Primary key SELECT Name FROM sys.objects WHERE type = @TypeOfKey AND parent_object_id = OBJECT_ID (@TableName) 

Posso sugerir uma resposta simples mais precisa para a pergunta original abaixo

 SELECT KEYS.table_schema, KEYS.table_name, KEYS.column_name, KEYS.ORDINAL_POSITION FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE keys INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS ON cons.TABLE_SCHEMA = keys.TABLE_SCHEMA AND cons.TABLE_NAME = keys.TABLE_NAME AND cons.CONSTRAINT_NAME = keys.CONSTRAINT_NAME WHERE cons.CONSTRAINT_TYPE = 'PRIMARY KEY' 

Notas:

  1. Algumas das respostas acima estão sem um filtro apenas para colunas de chave primária!
  2. Estou usando abaixo em um CTE para ingressar em uma listview de coluna maior para fornecer os metadados de uma fonte para alimentar a geração BIML de tabelas de migration de dados e o código SSIS

Pode ser postado recentemente, mas esperamos que isso ajude alguém a ver a lista de chaves primárias no sql server usando esta consulta t-sql:

 SELECT schema_name(t.schema_id) AS [schema_name], t.name AS TableName, COL_NAME(ic.OBJECT_ID,ic.column_id) AS PrimaryKeyColumnName, i.name AS PrimaryKeyConstraintName FROM sys.tables t INNER JOIN sys.indexes AS i on t.object_id=i.object_id INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id WHERE OBJECT_NAME(ic.OBJECT_ID) = 'YourTableNameHere' 

Você pode ver a lista de todas as foreign keys usando esta consulta se desejar:

 SELECT f.name as ForeignKeyConstraintName ,OBJECT_NAME(f.parent_object_id) AS ReferencingTableName ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ReferencingColumnName ,OBJECT_NAME (f.referenced_object_id) AS ReferencedTableName ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferencedColumnName ,delete_referential_action_desc AS DeleteReferentialActionDesc ,update_referential_action_desc AS UpdateReferentialActionDesc FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id --WHERE OBJECT_NAME(f.parent_object_id) = 'YourTableNameHere' --If you want to know referecing table details WHERE OBJECT_NAME(f.referenced_object_id) = 'YourTableNameHere' --If you want to know refereced table details ORDER BY f.name 

Eu encontrei isso do meu amigo, muito eficaz se você estiver procurando por todas as chaves primárias da tabela sob esquema específico.

 SELECT tc.constraint_name AS IndexName,tc.table_name AS TableName,tc.table_schema AS SchemaName,kc.column_name AS COLUMN_NAME FROM information_schema.table_constraints tc,information_schema.key_column_usage kc WHERE tc.constraint_type = 'PRIMARY KEY' AND kc.table_name = tc.table_name AND kc.table_schema = tc.table_schema AND kc.constraint_name = tc.constraint_name AND tc.table_schema='' 

Se você estiver procurando fazer seu próprio ORM ou gerar código a partir de uma determinada tabela, isso pode ser o que você está procurando:

 declare @table varchar(100) = 'mytable'; with cte as ( select tc.CONSTRAINT_SCHEMA , tc.CONSTRAINT_TYPE , tc.TABLE_NAME , ccu.COLUMN_NAME , IS_NULLABLE , DATA_TYPE , CHARACTER_MAXIMUM_LENGTH , NUMERIC_PRECISION from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu on tc.TABLE_NAME=ccu.TABLE_NAME and tc.TABLE_SCHEMA=ccu.TABLE_SCHEMA inner join information_schema.COLUMNS c on ccu.COLUMN_NAME=c.COLUMN_NAME and ccu.TABLE_NAME=c.TABLE_NAME and ccu.TABLE_SCHEMA=c.TABLE_SCHEMA where tc.table_name=@table and ccu.CONSTRAINT_NAME=tc.CONSTRAINT_NAME union select TABLE_SCHEMA,'COLUMN', TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@table and COLUMN_NAME not in (select COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = @table) ) select cast(iif(CONSTRAINT_TYPE='PRIMARY KEY',1,0) as bit) PrimaryKey ,cast(iif(CONSTRAINT_TYPE='FOREIGN KEY',1,0) as bit) ForeignKey ,cast(iif(CONSTRAINT_TYPE='COLUMN',1,0) as bit) NotKey ,COLUMN_NAME ,cast(iif(is_nullable='NO',0,1) as bit) IsNullable , DATA_TYPE , CHARACTER_MAXIMUM_LENGTH , NUMERIC_PRECISION from cte order by case CONSTRAINT_TYPE when 'PRIMARY KEY' then 1 when 'FOREIGN KEY' then 2 else 3 end , COLUMN_NAME 

Aqui está como o resultado seria:

  
PrimaryKey ForeignKey NotKey COLUMN_NAME IsNullable DATA_TYPE CHARACTER_MAXIMUM_LENGTH NUMERIC_PRECISION
1 0 0 LectureNoteID 0 int NULL 10
0 1 0 LectureId 0 int NULL 10
0 1 0 NoteTypeID 0 int NULL 10
0 0 1 Body 0 nvarchar -1 NULL
0 0 1 DisplayOrder 0 int NULL 10

Se chave primária e tipo necessários, esta consulta pode ser útil:

 SELECT L.TABLE_SCHEMA, L.TABLE_NAME, L.COLUMN_NAME, R.TypeName FROM( SELECT COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1 )L LEFT JOIN ( SELECT OBJECT_NAME(c.OBJECT_ID) TableName ,c.name AS ColumnName ,t.name AS TypeName FROM sys.columns AS c JOIN sys.types AS t ON c.user_type_id=t.user_type_id )R ON L.COLUMN_NAME = R.ColumnName AND L.TABLE_NAME = R.TableName