I'm looking for a little bit more usefule information than those tables provide. I'd like to use the Information_schema table to get to the table keys. Using something like:
SELECT * FROM INFORMATION_SCHEMA.Tables
but would like to actually display the table names and the leys within it - Do you know what the syntax would be?
In case anybody is interested, this works:
, kcu.ORDINAL_POSITION -- Position in the key
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.TABLE_CATALOG = kcu.TABLE_CATALOG
AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
AND tc.TABLE_NAME = kcu.TABLE_NAME
AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.[COLUMNS] c
ON tc.TABLE_CATALOG = c.TABLE_CATALOG
AND tc.TABLE_SCHEMA = c.TABLE_SCHEMA
AND tc.TABLE_NAME = c.TABLE_NAME
AND kcu.COLUMN_NAME= c.COLUMN_NAME
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND tc.TABLE_NAME != 'dtproperties'
ORDER BY tc.TABLE_NAME
Yes, they actually do. Microsoft doesn't guarantee that the sys tables won't change and for backwards compatibility, better to use the views; Since they don't change. However, having said that - I still like yours better!