SQL Server Get table primary key using sql query

Question

I want to get a particular table s primary key using SQL query for SQL Server database   In MySQL I am using following query to get table primary key   SHOW KEYS FROM tablename WHERE Key name    PRIMARY    What is equivalent of above query for SQL Server     If There is a query that will work for both MySQL and SQL Server then It will be an ideal case

User · Answer

select    from sysobjects  where xtype  pk  and     parent obj in  select id from sysobjects where name  tablename     this will work in sql 2005

User · Answer

Using SQL SERVER 2005  you can try  SELECT  i name AS IndexName          OBJECT NAME ic OBJECT ID  AS TableName          COL NAME ic OBJECT ID ic column id  AS ColumnName FROM    sys indexes AS i INNER JOIN          sys index columns AS ic ON  i OBJECT ID   ic OBJECT ID                                 AND i index id   ic index id WHERE   i is primary key   1   Found at SQL SERVER     2005     Find Tables With Primary Key Constraint in Database

User · Answer

It is also  Transact-SQL      according to BOL    -- exec sp serveroption  SERVER NAME    data access    true  --execute once    EXEC sp primarykeys  table server   N server name       table name   N table name      table catalog   N db name       table schema   N schema name   --frequently  dbo

User · Answer

This should list all the constraints and at the end you can put your filters     CAST IS DONE   SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT   WITH   ALL KEYS IN TABLE  CONSTRAINT NAME CONSTRAINT TYPE PARENT TABLE NAME PARENT COL NAME PARENT COL NAME DATA TYPE REFERENCE TABLE NAME REFERENCE COL NAME   AS   SELECT  CONSTRAINT NAME  CAST  PKnUKEY name AS VARCHAR 30             CONSTRAINT TYPE CAST  PKnUKEY type desc AS VARCHAR 30             PARENT TABLE NAME CAST  PKnUTable name AS VARCHAR 30             PARENT COL NAME CAST   PKnUKEYCol name AS VARCHAR 30             PARENT COL NAME DATA TYPE   oParentColDtl DATA TYPE                  REFERENCE TABLE NAME              REFERENCE COL NAME      FROM sys key constraints as PKnUKEY     INNER JOIN sys tables as PKnUTable             ON PKnUTable object id   PKnUKEY parent object id     INNER JOIN sys index columns as PKnUColIdx             ON PKnUColIdx object id   PKnUTable object id             AND PKnUColIdx index id   PKnUKEY unique index id     INNER JOIN sys columns as PKnUKEYCol             ON PKnUKEYCol object id   PKnUTable object id             AND PKnUKEYCol column id   PKnUColIdx column id      INNER JOIN INFORMATION SCHEMA COLUMNS oParentColDtl             ON oParentColDtl TABLE NAME PKnUTable name             AND oParentColDtl COLUMN NAME PKnUKEYCol name UNION ALL SELECT  CONSTRAINT NAME  CAST  oConstraint name AS VARCHAR 30             CONSTRAINT TYPE  FK           PARENT TABLE NAME CAST  oParent name AS VARCHAR 30             PARENT COL NAME CAST   oParentCol name AS VARCHAR 30             PARENT COL NAME DATA TYPE  oParentColDtl DATA TYPE               REFERENCE TABLE NAME CAST   oReference name AS VARCHAR 30             REFERENCE COL NAME CAST  oReferenceCol name AS VARCHAR 30    FROM sys foreign key columns FKC     INNER JOIN sys sysobjects oConstraint             ON FKC constraint object id oConstraint id      INNER JOIN sys sysobjects oParent             ON FKC parent object id oParent id     INNER JOIN sys all columns oParentCol             ON FKC parent object id oParentCol object id    ID of the object to which this column belongs                AND FKC parent column id oParentCol column id   ID of the column  Is unique within the object Column IDs might not be sequential        INNER JOIN sys sysobjects oReference             ON FKC referenced object id oReference id     INNER JOIN INFORMATION SCHEMA COLUMNS oParentColDtl             ON oParentColDtl TABLE NAME oParent name             AND oParentColDtl COLUMN NAME oParentCol name     INNER JOIN sys all columns oReferenceCol             ON FKC referenced object id oReferenceCol object id    ID of the object to which this column belongs                AND FKC referenced column id oReferenceCol column id   ID of the column  Is unique within the object Column IDs might not be sequential        select   from   ALL KEYS IN TABLE where        PARENT TABLE NAME  in   YOUR TABLE NAME        or REFERENCE TABLE NAME  in   YOUR TABLE NAME   ORDER BY PARENT TABLE NAME CONSTRAINT NAME    For reference please read thru - http   blogs msdn com b sqltips archive 2005 09 16 469136 aspx

User · Answer

I also found another one for SQL Server   SELECT COLUMN NAME FROM INFORMATION SCHEMA KEY COLUMN USAGE WHERE OBJECTPROPERTY OBJECT ID CONSTRAINT SCHEMA         QUOTENAME CONSTRAINT NAME     IsPrimaryKey     1 AND TABLE NAME    TableName  AND TABLE SCHEMA    Schema

User · Answer

From memory  it s either this   SELECT   FROM sys objects WHERE type    PK   AND  object id   OBJECT ID   tableName     or this     SELECT   FROM sys objects WHERE type    PK   AND  parent object id   OBJECT ID   tableName     I think one of them should probably work depending on how the data is stored but I am afraid I have no access to SQL to actually verify the same

User · Answer

SELECT COLUMN NAME FROM  DATABASENAME  INFORMATION SCHEMA KEY COLUMN USAGE WHERE TABLE NAME LIKE   TABLENAME   AND CONSTRAINT NAME LIKE  PK        WHERE     DATABASENAME    your database from your server AND    TABLENAME       your table name from which you want to see the primary key       NOTE   enter your database name and table name without brackets

User · Answer

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

User · Answer

The code I ll give you works and retrieves not only keys  but a lot of data from a table in SQL Server  Is tested in SQL Server 2k5 2k8  dunno about 2k  Enjoy   SELECT DISTINCT     sys tables object id AS TableId      sys columns column id AS ColumnId      sys columns name AS ColumnName      sys types name AS TypeName      sys columns precision AS NumericPrecision      sys columns scale AS NumericScale      sys columns is nullable AS IsNullable          SELECT              COUNT column name          FROM              INFORMATION SCHEMA CONSTRAINT COLUMN USAGE          WHERE             TABLE NAME   sys tables name AND             CONSTRAINT NAME                       SELECT                     constraint name                     FROM                          INFORMATION SCHEMA TABLE CONSTRAINTS                     WHERE                         TABLE NAME   sys tables name AND                                             constraint type    PRIMARY KEY  AND                         COLUMN NAME   sys columns name                         AS IsPrimaryKey      sys columns max length   2 AS CharMaxLength   BUG   FROM      sys columns  sys types  sys tables  WHERE     sys tables object id   sys columns object id AND     sys types system type id   sys columns system type id AND     sys types user type id   sys columns user type id AND     sys tables name    TABLE  ORDER BY      IsPrimaryKey   You can use only the primary key part  but I think that the rest might become handy  Best regards  David

User · Answer

Keep in mind that if you want to get exact primary field you need to put TABLE NAME and TABLE SCHEMA into the condition   this solution should work   select COLUMN NAME from information schema KEY COLUMN USAGE  where CONSTRAINT NAME  PRIMARY  AND TABLE NAME  TABLENAME   AND TABLE SCHEMA  DATABASENAME

[sql] SQL Server: Get table primary key using sql query

Examples related to sql

Examples related to sql-server