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?
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