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 Constraint_Type = 'PRIMARY KEY'
AND Col.Table_Name = ''
Get all tables which has primary key with data type uniqueidentifier in SQL Server
Using sql server object catalog views:
select
IndexName = i.name
, SchemaName = schema_Name(t.schema_id)
, TableName = t.Name
, ColumnName = c.Name
, TypeName = type_name(c.user_type_id)
from sys.tables t
inner join sys.indexes as i on
t.object_id = i.object_id
and i.is_primary_key = 1
inner join sys.index_columns as ic on
i.object_id = ic.object_id
and i.index_id = ic.index_id
inner join sys.columns as c on
c.column_id = ic.column_id
and c.object_id = ic.object_id
and type_name(c.user_type_id) = 'uniqueidentifier'
rextester link: http://rextester.com/SAL18997
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 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'
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';
How to find table name using primary key name in sql?
you can try this out ::
SELECT table_name
FROM information_Schema.columns
WHERE column_name='dept_id'
and ordinal_position = 1;
Related Topics
Delete Duplicate Rows (Don't Delete All Duplicate)
Rails Query Through Association Limited to Most Recent Record
Accounting for Dst in Postgres, When Selecting Scheduled Items
What's the Difference Between Varchar and Char
How to Determine the Last Day of the Previous Month Using Postgresql
How to Use "Date" Datatype in SQL Server
Updating Table Rows in Postgres Using Subquery
Storing Money in a Decimal Column - What Precision and Scale
How to Store a List in a Column of a Database Table
Simulate Create Database If Not Exists For Postgresql
SQL Statement to Select All Rows from Previous Day
How to Install Freetds in Linux
Optimise Postgresql For Fast Testing
Delete Duplicate Records in SQL Server