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
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.
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>'
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
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'
Find all tables in database By primaryKey
You can use this query. Replace 'YOUR_COLUMN_NAME' with your primary key column name.
SELECT Table_Name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
and COLUMN_NAME ='YOUR_COLUMN_NAME'
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';
How to find the name and value of a table's primary key in DB2
This should give you what you need on mainframe:
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/E.8?DT=20010718164132
DB2 10 Z/OS:
function:
SQLPrimaryKeys()
http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/odbc/src/tpc/db2z_fnprimarykeys.html
How to get primary key of table?
A better way is to use SHOW KEYS
since you don't always have access to information_schema. The following works:
SHOW KEYS FROM table WHERE Key_name = 'PRIMARY'
Column_name will contain the name of the primary key.
Related Topics
When to Use an Auto-Incremented Primary Key and When Not To
Difference Between Int Primary Key and Integer Primary Key SQLite
Determine a Table's Primary Key Using Tsql
How to Count the Number of Columns in a Table Using SQL
How to Update All Columns with Insert ... on Conflict ...
Does SQL Server Allow Constraint Violations in a Transaction as Long as It's Not Committed Yet
Use Row Values as Columns in Postgresql
Performance Difference: Condition Placed at Inner Join VS Where Clause
Best Way to Interpolate Values in SQL
Add an Incremental Number in a Field in Insert into Select Query in SQL Server
Conditional Join Different Tables
Postgres - Create Table from Select
How to Add Results of Two Select Commands in Same Query
Entity Framework: How to Properly Handle Exceptions That Occur Due to SQL Constraints