Todd Sherman (11/7/2007)
Howdy, I expanded this a bit, and its now my favorite query for a new database 🙂By adding the bit to the WHERE clause, it brings back information on all the key fields so its a little more generic and useful for a first glance.
SELECT base.TABLE_NAME, base.COLUMN_NAME, cons.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.COLUMNS base
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE usage ON
(base.TABLE_SCHEMA = usage.TABLE_SCHEMA
and base.TABLE_NAME = usage.TABLE_NAME
and base.COLUMN_NAME = usage.COLUMN_NAME)
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS cons on
(base.TABLE_SCHEMA = usage.TABLE_SCHEMA
and usage.CONSTRAINT_NAME = cons.CONSTRAINT_NAME
and usage.TABLE_NAME = cons.TABLE_NAME)
WHERE base.COLUMN_NAME in
(SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME LIKE '%_pk')
ORDER BY 1, 2
Probably if you want all the constraints in the database you may want to just use something like
SELECT base.TABLE_NAME,
base.COLUMN_NAME,
cons.CONSTRAINT_TYPE
FROM
INFORMATION_SCHEMA.COLUMNS base
LEFT JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE usage ON (base.TABLE_SCHEMA = usage.TABLE_SCHEMA
and
base.TABLE_NAME = usage.TABLE_NAME
and
base.COLUMN_NAME = usage.COLUMN_NAME)
LEFT JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS cons on (base.TABLE_SCHEMA = usage.TABLE_SCHEMA and usage.CONSTRAINT_NAME = cons.CONSTRAINT_NAME
and
usage.TABLE_NAME = cons.TABLE_NAME)
WHERE
cons.CONSTRAINT_TYPE IS NOT NULL
or if you want to check for only Primary key or foreign keys you may change your where clause to include the filter on cons.CONSTRAINT_TYPE = @Constraint_type
Assuming you replace @Constraint_type with appropriate filter condition.
Prasad Bhogadi
www.inforaise.com