SQL Server: Get table primary key using sql query

I want to get a particular table's primary key using SQL query for SQL Server database.

In MySQL I am using following query to get table primary key:

SHOW KEYS FROM tablename WHERE Key_name = 'PRIMARY'

What is equivalent of above query for SQL Server ?.

If There is a query that will work for both MySQL and SQL Server then It will be an ideal case.

I also found another one for SQL Server:

SELECT 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'

Found another one:

SELECT 
	 KU.table_name as TABLENAME
	,column_name as PRIMARYKEYCOLUMN
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
	ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 
	AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME 
	AND KU.table_name='YourTableName'

ORDER BY 
	 KU.TABLE_NAME
	,KU.ORDINAL_POSITION
; 

I have tested this on SQL Server 2003/2005

Using SQL SERVER 2005, you can try

SELECT	i.name AS IndexName,
		OBJECT_NAME(ic.OBJECT_ID) AS TableName,
		COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM	sys.indexes AS i INNER JOIN 
		sys.index_columns AS ic	ON	i.OBJECT_ID = ic.OBJECT_ID
								AND i.index_id = ic.index_id
WHERE	i.is_primary_key = 1

Found at SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database

From memory, it's either this

SELECT * FROM sys.objects
WHERE type = 'PK' 
AND  object_id = OBJECT_ID ('tableName')

or this..

SELECT * FROM sys.objects
WHERE type = 'PK' 
AND  parent_object_id = OBJECT_ID ('tableName')

I think one of them should probably work depending on how the data is stored but I am afraid I have no access to SQL to actually verify the same.

SELECT COLUMN_NAME FROM {DATABASENAME}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME LIKE '{TABLENAME}' AND CONSTRAINT_NAME LIKE 'PK%'

> WHERE
> {DATABASENAME} = your database from your server AND
> {TABLENAME} = your table name from which you want to see the primary key. > > NOTE : enter your database name and table name without brackets.

select * 
from sysobjects 
where xtype='pk' and 
   parent_obj in (select id from sysobjects where name='tablename')

this will work in sql 2005

The code I'll give you works and retrieves not only keys, but a lot of data from a table in SQL Server. Is tested in SQL Server 2k5/2k8, dunno about 2k. Enjoy!

SELECT DISTINCT
	sys.tables.object_id AS TableId,
	sys.columns.column_id AS ColumnId,
	sys.columns.name AS ColumnName,
	sys.types.name AS TypeName,
	sys.columns.precision AS NumericPrecision,
	sys.columns.scale AS NumericScale,
	sys.columns.is_nullable AS IsNullable,
	( 	SELECT 
			COUNT(column_name)
		FROM 
			INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 
		WHERE
			TABLE_NAME = sys.tables.name AND
			CONSTRAINT_NAME =
				(	SELECT
					constraint_name
					FROM 
						INFORMATION_SCHEMA.TABLE_CONSTRAINTS
					WHERE
						TABLE_NAME = sys.tables.name AND					
						constraint_type = 'PRIMARY KEY' AND
						COLUMN_NAME = sys.columns.name
				)
	) AS IsPrimaryKey,
	sys.columns.max_length / 2 AS CharMaxLength /*BUG*/
FROM 
	sys.columns, sys.types,	sys.tables 
WHERE
	sys.tables.object_id = sys.columns.object_id AND
	sys.types.system_type_id = sys.columns.system_type_id AND
	sys.types.user_type_id = sys.columns.user_type_id AND
	sys.tables.name = 'TABLE'
ORDER BY 
	IsPrimaryKey

You can use only the primary key part, but I think that the rest might become handy. Best regards, David

This should list all the constraints and at the end you can put your filters

/* 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

Keep in mind that if you want to get exact primary field you need to put TABLE_NAME and TABLE_SCHEMA into the condition.

this solution should work:

select COLUMN_NAME from information_schema.KEY_COLUMN_USAGE 
where CONSTRAINT_NAME='PRIMARY' AND TABLE_NAME='TABLENAME' 
AND TABLE_SCHEMA='DATABASENAME'

It is also (Transact-SQL) ... according to BOL.

-- exec sp_serveroption 'SERVER NAME', 'data access', 'true' --execute once  

EXEC sp_primarykeys @table_server = N'server_name', 
  @table_name = N'table_name',
  @table_catalog = N'db_name', 
  @table_schema = N'schema_name'; --frequently 'dbo'