Simple question, how do you list the primary key of a table with T-SQL? I know how to get indexes on a table, but can't remember how to get the PK.
This question is related to
sql
sql-server
tsql
The answer is
Sys.Objects Table contains row for each user-defined, schema-scoped object .
Constraints created like Primary Key or others will be the object and Table name will be the parent_object
Query sys.Objects and collect the Object's Ids of Required Type
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)
If Primary Key and type needed, this query may be useful:
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
May I suggest a more accurate simple answer to the original question below
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'
Notes:
- Some of the answers above are missing a filter for just primary key columns!
- I'm using below in a CTE to join to a larger column listing to provide the metadata from a source to feed BIML generation of staging tables and SSIS code
I like the INFORMATION_SCHEMA technique, but another I've used is: exec sp_pkeys 'table'
It's generally recommended practice now to use the sys.* views over INFORMATION_SCHEMA in SQL Server, so unless you're planning on migrating databases I would use those. Here's how you would do it with the sys.* views:
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('<schema>.<tablename>');
May I suggest a more accurate simple answer to the original question below
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'
Notes:
- Some of the answers above are missing a filter for just primary key columns!
- I'm using below in a CTE to join to a larger column listing to provide the metadata from a source to feed BIML generation of staging tables and SSIS code
Below query will list primary keys of particular table:
SELECT DISTINCT
CONSTRAINT_NAME AS [Constraint],
TABLE_SCHEMA AS [Schema],
TABLE_NAME AS TableName
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_NAME = 'mytablename'
Is using MS SQL Server you can do the following:
--List all tables primary keys
select * from information_schema.table_constraints
where constraint_type = 'Primary Key'
You can also filter on the table_name column if you want a specific table.
Here's another way from the question get table primary key using sql query:
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA+'.'+CONSTRAINT_NAME), 'IsPrimaryKey') = 1
AND TABLE_NAME = '<your table name>'
It uses KEY_COLUMN_USAGE to determine the constraints for a given table
Then uses OBJECTPROPERTY(id, 'IsPrimaryKey') to determine if each is a primary key
Thanks Guy.
With a slight variation I used it to find all the primary keys for all the tables.
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
If you are looking to do your own ORM or generate code from a given table, then this might be what you are looking form:
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
Here is what the result would look like:
_x000D_
_x000D_
_x000D_
_x000D_
<table cellspacing=0 border=1>_x000D_
<tr>_x000D_
<td style=min-width:50px>PrimaryKey</td>_x000D_
<td style=min-width:50px>ForeignKey</td>_x000D_
<td style=min-width:50px>NotKey</td>_x000D_
<td style=min-width:50px>COLUMN_NAME</td>_x000D_
<td style=min-width:50px>IsNullable</td>_x000D_
<td style=min-width:50px>DATA_TYPE</td>_x000D_
<td style=min-width:50px>CHARACTER_MAXIMUM_LENGTH</td>_x000D_
<td style=min-width:50px>NUMERIC_PRECISION</td>_x000D_
</tr>_x000D_
<tr>_x000D_
<td style=min-width:50px>1</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>LectureNoteID</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>int</td>_x000D_
<td style=min-width:50px>NULL</td>_x000D_
<td style=min-width:50px>10</td>_x000D_
</tr>_x000D_
<tr>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>1</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>LectureId</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>int</td>_x000D_
<td style=min-width:50px>NULL</td>_x000D_
<td style=min-width:50px>10</td>_x000D_
</tr>_x000D_
<tr>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>1</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>NoteTypeID</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>int</td>_x000D_
<td style=min-width:50px>NULL</td>_x000D_
<td style=min-width:50px>10</td>_x000D_
</tr>_x000D_
<tr>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>1</td>_x000D_
<td style=min-width:50px>Body</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>nvarchar</td>_x000D_
<td style=min-width:50px>-1</td>_x000D_
<td style=min-width:50px>NULL</td>_x000D_
</tr>_x000D_
<tr>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>1</td>_x000D_
<td style=min-width:50px>DisplayOrder</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>int</td>_x000D_
<td style=min-width:50px>NULL</td>_x000D_
<td style=min-width:50px>10</td>_x000D_
</tr>_x000D_
</table>_x000D_
I am telling a simple Technic which I follow
SP_HELP 'table_name'
run this code as query. Mention your table name at place of table_name for which you want to know Primary Key (don't forget the single quotes). The result will show like attached Image. Hope it will help you
I found this useful, gives a list of tables with a comma separate list of the columns and then also a comma separate list of which ones are the primary key
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
I found this from my friend, very effective if you are looking for all the table's primary keys under particular schema.
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='<SCHEMA_NAME>'
If you are looking to do your own ORM or generate code from a given table, then this might be what you are looking form:
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
Here is what the result would look like:
_x000D_
_x000D_
_x000D_
_x000D_
<table cellspacing=0 border=1>_x000D_
<tr>_x000D_
<td style=min-width:50px>PrimaryKey</td>_x000D_
<td style=min-width:50px>ForeignKey</td>_x000D_
<td style=min-width:50px>NotKey</td>_x000D_
<td style=min-width:50px>COLUMN_NAME</td>_x000D_
<td style=min-width:50px>IsNullable</td>_x000D_
<td style=min-width:50px>DATA_TYPE</td>_x000D_
<td style=min-width:50px>CHARACTER_MAXIMUM_LENGTH</td>_x000D_
<td style=min-width:50px>NUMERIC_PRECISION</td>_x000D_
</tr>_x000D_
<tr>_x000D_
<td style=min-width:50px>1</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>LectureNoteID</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>int</td>_x000D_
<td style=min-width:50px>NULL</td>_x000D_
<td style=min-width:50px>10</td>_x000D_
</tr>_x000D_
<tr>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>1</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>LectureId</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>int</td>_x000D_
<td style=min-width:50px>NULL</td>_x000D_
<td style=min-width:50px>10</td>_x000D_
</tr>_x000D_
<tr>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>1</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>NoteTypeID</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>int</td>_x000D_
<td style=min-width:50px>NULL</td>_x000D_
<td style=min-width:50px>10</td>_x000D_
</tr>_x000D_
<tr>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>1</td>_x000D_
<td style=min-width:50px>Body</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>nvarchar</td>_x000D_
<td style=min-width:50px>-1</td>_x000D_
<td style=min-width:50px>NULL</td>_x000D_
</tr>_x000D_
<tr>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>1</td>_x000D_
<td style=min-width:50px>DisplayOrder</td>_x000D_
<td style=min-width:50px>0</td>_x000D_
<td style=min-width:50px>int</td>_x000D_
<td style=min-width:50px>NULL</td>_x000D_
<td style=min-width:50px>10</td>_x000D_
</tr>_x000D_
</table>_x000D_
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
I like the INFORMATION_SCHEMA technique, but another I've used is: exec sp_pkeys 'table'
This one gives you the columns that are PK.
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'TableName'
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
Is using MS SQL Server you can do the following:
--List all tables primary keys
select * from information_schema.table_constraints
where constraint_type = 'Primary Key'
You can also filter on the table_name column if you want a specific table.
The system stored procedure sp_help will give you the information. Execute the following statement:
execute sp_help table_name
This should list all the constraints ( primary Key and Foreign Keys ) and at the end of query put table name
/* 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;
For reference please read thru - http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx
The system stored procedure sp_help will give you the information. Execute the following statement:
execute sp_help table_name
Is using MS SQL Server you can do the following:
--List all tables primary keys
select * from information_schema.table_constraints
where constraint_type = 'Primary Key'
You can also filter on the table_name column if you want a specific table.
The system stored procedure sp_help will give you the information. Execute the following statement:
execute sp_help table_name
This is a solution which uses only sys-tables.
It lists all the primary keys in the database. It returns schema, table name, column name and the correct column sort order for each primary key.
If you want to get the primary key for a specific table, then you need to filter on SchemaName and TableName.
IMHO, this solution is very generic and does not use any string literals, so it will run on any machine.
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 ;
The system stored procedure sp_help will give you the information. Execute the following statement:
execute sp_help table_name
This is a solution which uses only sys-tables.
It lists all the primary keys in the database. It returns schema, table name, column name and the correct column sort order for each primary key.
If you want to get the primary key for a specific table, then you need to filter on SchemaName and TableName.
IMHO, this solution is very generic and does not use any string literals, so it will run on any machine.
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 ;
Below query will list primary keys of particular table:
SELECT DISTINCT
CONSTRAINT_NAME AS [Constraint],
TABLE_SCHEMA AS [Schema],
TABLE_NAME AS TableName
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_NAME = 'mytablename'
--This is another Modified Version which is also an example for Co-Related Query
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')
Might be lately posted but hopefully this will help someone to see primary key list in sql server by using this t-sql query:
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'
You can see the list of all foreign keys by using this query if you may want:
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
I am telling a simple Technic which I follow
SP_HELP 'table_name'
run this code as query. Mention your table name at place of table_name for which you want to know Primary Key (don't forget the single quotes). The result will show like attached Image. Hope it will help you
For a comma separated list of primary key columns for a given TableName and Schema:
Select distinct SUBSTRING ( stuff(( select distinct ',' + [COLUMN_NAME]
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND TABLE_NAME = 'TableName' AND TABLE_SCHEMA = 'Schema'
order by 1 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,0,'' )
,2,9999)
It's generally recommended practice now to use the sys.* views over INFORMATION_SCHEMA in SQL Server, so unless you're planning on migrating databases I would use those. Here's how you would do it with the sys.* views:
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('<schema>.<tablename>');
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
Give this a try:
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'
For a comma separated list of primary key columns for a given TableName and Schema:
Select distinct SUBSTRING ( stuff(( select distinct ',' + [COLUMN_NAME]
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND TABLE_NAME = 'TableName' AND TABLE_SCHEMA = 'Schema'
order by 1 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,0,'' )
,2,9999)
Might be lately posted but hopefully this will help someone to see primary key list in sql server by using this t-sql query:
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'
You can see the list of all foreign keys by using this query if you may want:
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
Here's another way from the question get table primary key using sql query:
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA+'.'+CONSTRAINT_NAME), 'IsPrimaryKey') = 1
AND TABLE_NAME = '<your table name>'
It uses KEY_COLUMN_USAGE to determine the constraints for a given table
Then uses OBJECTPROPERTY(id, 'IsPrimaryKey') to determine if each is a primary key
--This is another Modified Version which is also an example for Co-Related Query
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')
I found this from my friend, very effective if you are looking for all the table's primary keys under particular schema.
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='<SCHEMA_NAME>'
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
Is using MS SQL Server you can do the following:
--List all tables primary keys
select * from information_schema.table_constraints
where constraint_type = 'Primary Key'
You can also filter on the table_name column if you want a specific table.
I like the INFORMATION_SCHEMA technique, but another I've used is: exec sp_pkeys 'table'
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
This version displays the schema, the table name and an ordered, comma separated list of primary keys. Object_Id() does not work for link servers so we filter by the table name.
Without the REPLACE(Si1.Column_Name, '', '') it would show the xml opening and closing tags for Column_Name on the database I was testing on. I am not sure why the database required a replace for 'Column_Name' so if someone knows then please comment.
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;
And the same pattern using George's query:
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;
Sys.Objects Table contains row for each user-defined, schema-scoped object .
Constraints created like Primary Key or others will be the object and Table name will be the parent_object
Query sys.Objects and collect the Object's Ids of Required Type
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)
Give this a try:
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'
I found this useful, gives a list of tables with a comma separate list of the columns and then also a comma separate list of which ones are the primary key
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
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
This one gives you the columns that are PK.
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'TableName'
This should list all the constraints ( primary Key and Foreign Keys ) and at the end of query put table name
/* 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;
For reference please read thru - http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx
Give this a try:
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'
This version displays the schema, the table name and an ordered, comma separated list of primary keys. Object_Id() does not work for link servers so we filter by the table name.
Without the REPLACE(Si1.Column_Name, '', '') it would show the xml opening and closing tags for Column_Name on the database I was testing on. I am not sure why the database required a replace for 'Column_Name' so if someone knows then please comment.
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;
And the same pattern using George's query:
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;
If Primary Key and type needed, this query may be useful:
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
Source: Stackoverflow.com