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

Question

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

Answer 1

You can dig that info up in information_schema.table_constraints and information_schema.constraint_column_usage tables, by checking for multiple rows of PRIMARY KEY constraints on a table, something like:

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

An SQLfiddle to test with.