SQL Server: Get Table Primary Key Using SQL Query

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



Leave a reply



Submit