Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Locate primary key - foreign key relations Expand / Collapse
Author
Message
Posted Friday, October 05, 2007 1:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 7:36 PM
Points: 148, Visits: 69
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"
Post #407565
Posted Wednesday, November 07, 2007 5:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 12, 2010 7:12 AM
Points: 69, Visits: 148
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

Post #419550
Posted Wednesday, November 07, 2007 5:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 6:47 AM
Points: 967, Visits: 450
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
Post #419557
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse