Geek Answers Handbook

How to get a list of tables with a composite primary key in SQL Server?

How to create a query that gives me a list of tables with a composite primary key in SQL Server? Maybe use sys.tables or information_schema.tables or something else?

+6
source share
2 answers

You can dig this information in the information_schema.table_constraints and information_schema.constraint_column_usage tables by checking for multiple PRIMARY KEY rows in the table, for example:

 SELECT col.table_name FROM information_schema.table_constraints tc JOIN information_schema.constraint_column_usage col ON col.constraint_name = tc.constraint_name AND col.table_name = tc.table_name AND tc.constraint_type = 'PRIMARY KEY' GROUP BY col.table_name HAVING COUNT(*) > 1

SQLfiddle for testing .

+11
source

You need to use a table of tables or eigher columns. Here he is.

 SELECT K.TABLE_CATALOG, K.TABLE_NAME, K.COLUMN_NAME, K.ORDINAL_POSITION FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE K INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON K.TABLE_CATALOG = TC.TABLE_CATALOG AND K.TABLE_SCHEMA = TC.TABLE_SCHEMA AND K.CONSTRAINT_NAME = TC.CONSTRAINT_NAME WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'

Link to Link - Getting column information (composite key) in SQL

http://blog.sqlauthority.com/2007/09/04/sql-server-2005-find-tables-with-primary-key-constraint-in-database/

+3
source

More articles:


All Articles