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 = ''
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';
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 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.
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.
Get a List of all Primary Keys in a Database
look on link
EXEC sp_pkeys ''
EXEC sp_helpconstraint ''
sp_pkeys will return a row for each
column that participates in the
primary key for . The
columns you are likely most interested
in are COLUMN_NAME and PK_NAME.sp_helpconstraint will list all
constraints for , including
foreign keys that reference the table.
In the first recordset, there will
only be a column called Object Name
(kind of useless, since that's what
you passed in). In the second
resultset, there will be the following
columns: constraint_type,
constraint_name, and constraint_keys.
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
Related Topics
Convert Varchar into Datetime in SQL Server
Generate a Resultset of Incrementing Dates in Tsql
Is Having an 'Or' in an Inner Join Condition a Bad Idea
How to Create a Sequence in MySQL
Fastest Way to Remove Non-Numeric Characters from a Varchar in SQL Server
How to Query a Tree Structure Table in MySQL in a Single Query, to Any Depth
Sql: Between VS ≪= and ≫=
Line Count with in the Text Files Having Multiple Lines and Single Lines
SQL Server: Howto Get Foreign Key Reference from Information_Schema
MySQL Unknown Column in on Clause
Multiple Inner Join SQL Access
T-SQL Datetime Rounded to Nearest Minute and Nearest Hours With Using Functions
SQL Server - in Clause With a Declared Variable
Using an Alias in SQL Calculations
What Are Your Most Common SQL Optimizations
Importance of Varchar Length in MySQL Table
What Is the Best Free SQL Gui for Linux for Various Dbms Systems