I'd like to determine the primary key of a table using TSQL (stored procedure or system table is fine). Is there such a mechanism in SQL Server (2005 or 2008)?
+4
A:
This should get you started:
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name
WHERE tc.CONSTRAINT_TYPE = 'Primary Key'
HTH, Stu
Stuart Ainsworth
2009-10-06 13:48:05
+2
A:
SELECT ccu.COLUMN_NAME, ccu.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu
ON tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
WHERE tc.TABLE_CATALOG = 'Your_Catalog' -- replace with your catalog
AND tc.TABLE_SCHEMA = 'dbo' -- replace with your schema
AND tc.TABLE_NAME = 'Your_Table' -- replace with your table name
AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
LukeH
2009-10-06 13:53:48
+1
A:
Here's one based on system tables from SQL 2005 (99% sure it'd work in 2008). This will list all PKs for all user-defined tables, with all columns and some extra fluff that could be removed. Add parameters to pick out a table at a time.
SELECT
schema_name(ta.schema_id) SchemaName
,ta.name TableName
,ind.name
,indcol.key_ordinal Ord
,col.name ColumnName
,ind.type_desc
,ind.fill_factor
from sys.tables ta
inner join sys.indexes ind
on ind.object_id = ta.object_id
inner join sys.index_columns indcol
on indcol.object_id = ta.object_id
and indcol.index_id = ind.index_id
inner join sys.columns col
on col.object_id = ta.object_id
and col.column_id = indcol.column_id
where ind.is_primary_key = 1
order by
ta.name
,indcol.key_ordinal
Philip Kelley
2009-10-06 13:55:28