SQLServerCentral.com / Discuss content posted by Mohit Nayyar / Article Discussions by Author / Article Discussions / Locate primary key - foreign key relations / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 18:59:01 GMT20RE: Locate primary key - foreign key relationshttp://www.sqlservercentral.com/Forums/Topic407565-613-1.aspxThanks for the script.Thu, 12 May 2016 07:05:32 GMTIwas BornreadyRE: Locate primary key - foreign key relationshttp://www.sqlservercentral.com/Forums/Topic407565-613-1.aspx[quote][b]Todd Sherman (11/7/2007)[/b][hr]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.[code]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[/code][/quote]Probably if you want all the constraints in the database you may want to just use something likeSELECT 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 NULLor 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_typeAssuming you replace @Constraint_type with appropriate filter condition.Wed, 07 Nov 2007 05:53:40 GMTPrasad BhogadiRE: Locate primary key - foreign key relationshttp://www.sqlservercentral.com/Forums/Topic407565-613-1.aspxHowdy, 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.[code]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[/code]Wed, 07 Nov 2007 05:32:49 GMTTodd Sherman-487999Locate primary key - foreign key relationshttp://www.sqlservercentral.com/Forums/Topic407565-613-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Miscellaneous/31983/">Locate primary key - foreign key relations</A>[/B]Fri, 05 Oct 2007 13:22:43 GMTMohit Nayyar