October 5, 2007 at 1:22 pm
Comments posted to this topic are about the item Locate primary key - foreign key relations
Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com/
"If I am destined to fail, then I do have a purpose in my life, To fail my destiny"
November 7, 2007 at 5:32 am
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
November 7, 2007 at 5:53 am
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
May 12, 2016 at 7:05 am
Thanks for the script.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply