Get a list of all the main keys in the database

Question

Is this the best way to get a list of all the main keys in the database - or is there something better?

SELECT KCU.TABLE_NAME AS Table_Name, KCU.CONSTRAINT_NAME AS Constraint_Name, KCU.COLUMN_NAME AS COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA AND KCU.TABLE_NAME = TC.TABLE_NAME WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY KCU.TABLE_SCHEMA, KCU.TABLE_NAME, KCU.CONSTRAINT_NAME 

Answer 1

look link

 EXEC sp_pkeys '<tablename>' EXEC sp_helpconstraint '<tablename>' 

sp_pkeys will return a row for each column that participates in the primary key for. the columns that interest you most are COLUMN_NAME and PK_NAME.

sp_helpconstraint will list all restrictions, including foreign keys that reference the table. In the first set of records there will be only a column named Object Name (it seems to be useless, since you passed it). In the second of results, there will be the following columns: constraint_type, constraint_name and constraint_keys.

Answer 2

 USE databasename; GO 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 

This query will retrieve all primary key constraints from the database ... You just need to execute this query and enter the database name in the first line

Answer 3

The following syntax gives you all the limitations in the database used.

 select * from sys.key_constraints; 

Answer 4

If you need data type information:

 SELECT so.name 'Table Name', c.name 'Column Name', t.Name 'Data type', c.max_length 'Max Length', c.precision , c.scale , c.is_nullable, ISNULL(i.is_primary_key, 0) 'Primary Key' FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id INNER JOIN sysobjects so ON c.object_id = so.id WHERE i.is_primary_key = 1 and so.xtype = 'U' Order By 'Table Name', 'Column Name' 

Answer 5

If you are doing this from java, you can also use the getPrimaryKeys method in the databasemetadata object. Other languages ​​may have similar ways of doing this.