Get Primary Key For Table Using SQL


If you are using SQL Server 2008 R2, and need to identify the primary key for a table using SQL, then you have at your disposal the system tables and INFORMATION_SCHEMA.

The code below provides the table id, table name, column id, column name, data type, and size for the specified table:

SELECT t.object_id AS TableId, t.name AS TableName, c.column_id AS ColumnId, c.name AS ColumnName, ISC.DATA_TYPE As DataType, ISC.CHARACTER_MAXIMUM_LENGTH AS Size
FROM sys.indexes i
LEFT JOIN sys.index_columns ic
ON i.index_id = ic.index_id
LEFT JOIN sys.columns c
ON ic.column_id = c.column_id
LEFT JOIN sys.tables t
ON c.object_id = t.object_id
LEFT JOIN sys.types ty
ON ty.system_type_id = c.system_type_id
AND c.user_type_id = ty.user_type_id
LEFT JOIN INFORMATION_SCHEMA.COLUMNS ISC
ON t.name = ISC.TABLE_NAME
AND c.name = ISC.COLUMN_NAME
WHERE i.is_primary_key = 1
AND ic.object_id = i.object_id
AND c.object_id = i.object_id
AND t.object_id = c.object_id
AND t.name = 'TableName'

The code above will work for tables that have more than one primary key, and primary keys that are numeric or text-based.

Advertisements