SQL Server: Get table primary key using sql query
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
How to determine the primary key for a table in SQL Server?
I use this in a code generator I wrote to get the primary key:
SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName,
c.is_identity, c.user_type_id, CAST(c.max_length AS int) AS max_length,
CAST(c.precision AS int) AS precision, CAST(c.scale AS int) AS scale
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1 AND ic.OBJECT_ID = OBJECT_ID('dbo.YourTableNameHere')
ORDER BY OBJECT_NAME(ic.OBJECT_ID), ic.key_ordinal
How do you list the primary key of a SQL Server table?
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 Tab.Constraint_Type = 'PRIMARY KEY'
AND Col.Table_Name = '<your table name>'
SQL query to find Primary Key of a table?
For Oracle, you can look it up in the ALL_CONSTRAINTS
table:
SELECT a.COLUMN_NAME
FROM all_cons_columns a INNER JOIN all_constraints c
ON a.constraint_name = c.constraint_name
WHERE c.table_name = 'TBL'
AND c.constraint_type = 'P';
DEMO.
For SQL Server, it was already answered here, and for MySQL check @ajon's answer.
Determine a table's primary key using TSQL
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.TABLE_NAME = 'TableName' AND
tc.CONSTRAINT_TYPE = 'Primary Key'
How do I determine if a column is in the primary key of its table? (SQL Server)
Here is one way (replace 'keycol' with the column name you are searching
for):
SELECT K.TABLE_NAME ,
K.COLUMN_NAME ,
K.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.TABLE_NAME = K.TABLE_NAME
AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND K.COLUMN_NAME = 'keycol';
Sql Server - Get Full Primary Key of table including column sort orders via a sql query
Something like this - you can add OBJECT_NAME(o.parent_object_id) = 'tablename' into the where for a single PK. With a small amount of tweaking it can give you the data for all indexes too.
SELECT OBJECT_SCHEMA_NAME(o.parent_object_id) AS [Schema], OBJECT_NAME(o.parent_object_id) AS [Table], o.name AS PrimaryKey, ic.index_id, ic.key_ordinal, c.name AS ColumnName, t.name AS DataType, ic.is_descending_key
from sys.objects o
inner join sys.indexes i on i.object_id = o.parent_object_id and i.is_primary_key = 1
inner join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id
inner join sys.columns c on c.object_id = o.parent_object_id and c.column_id = ic.column_id
inner join sys.types t ON t.user_type_id = c.user_type_id
where o.type = 'PK'
ORDER BY OBJECT_SCHEMA_NAME(o.parent_object_id), OBJECT_NAME(o.parent_object_id), ic.key_ordinal
On a more general note, using MS's DMV's will generally reveal more information than the INFORMATION_SCHEMA views, e.g. user data types & other SQL Server-specific features.
How do you list the primary key of a SQL Server User Defined Table Type?
This is stored in the catalog views:
SELECT c.Name
FROM sys.table_types AS tt
INNER JOIN sys.key_constraints AS kc
ON kc.parent_object_id = tt.type_table_object_id
INNER JOIN sys.indexes AS i
ON i.object_id = kc.parent_object_id
AND i.index_id = kc.unique_index_id
INNER JOIN sys.index_columns AS ic
ON ic.object_id = kc.parent_object_id
INNER JOIN sys.columns AS c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
WHERE tt.Name = 'YourTypeName';
Related Topics
Search All Databases for Value
How to Compare Two Columns in the Same Table
Sql: Try/Catch Doesn't Catch an Error When Attempting to Access a Table That It Can't Find
Set Empty Strings ('') to Null in the Whole Database
How to Speed Up This Indexed View
Tree Structure in SQL in Oracle.How to Show Tree,Child Nodes and Parent Nodes in SQL Oracle
How to Add New Column in Existing View in SQL-Server 2014 Using Alter
Reason for System.Transactions.Transactionindoubtexception
How to Merge Multiple Database Files in SQLite
JSON Without Array Wrapper on Lower Levels