How to find the tables a FK references?

[aalmeida]

I've been trying to create a query that would return me the drop and create Foreing Key constraints on every table of a database
I was able to create the Script to drop constraint but I've not been able to create the script to recreate the constaint because
I cannot find the table and column names of the referenced table see bellow:

SELECT   	'ALTER TABLE [' + t2.TABLE_SCHEMA+'.'+t2.Table_Name + 
		'] DROP CONSTRAINT ' + + t1.UNIQUE_CONSTRAINT_SCHEMA +
		'.'+t1.Constraint_Name 					as 'Script to Drop Constraint'
		, 'ALTER TABLE [' + t2.TABLE_SCHEMA +
		'].['+ t2.TABLE_NAME + 
		'] WITH NOCHECK ADD CONSTRAINT [' + t2.CONSTRAINT_SCHEMA +
		'].['+ t2.CONSTRAINT_NAME + 
		'] FOREIGN KEY ( '+ t2.COLUMN_NAME + 
		' ) REFERENCES ['+ t2.CONSTRAINT_SCHEMA +
		'].['+ t2.TABLE_NAME +
		'] ( ' + t2.COLUMN_NAME +
		' ) NOT FOR REPLICATION'  				as 'Script to Create Constraint with NOT FOR REPLICATION'
  FROM  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS t1 
	INNER JOIN  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t2 
		ON t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME
GO

Here is an example demonstraiting the problem:
Created by EM:
ALTER TABLE Table_1 WITH NOCHECK ADD CONSTRAINT fk04 FOREIGN KEY ( column_1 ) REFERENCES owner.Table_2 ( column_2 ) NOT FOR REPLICATION
Created by the Scrip:
ALTER TABLE [owner].[Table_1] WITH NOCHECK ADD CONSTRAINT [owner].[fk04] FOREIGN KEY ( [column_1] ) REFERENCES [owner].[Table_1] ( [column_1] ) NOT FOR REPLICATION

Any help is greatly apreciated.
Thanks,

AL Almeida
Senior DBA

http://www.ajmedia.net/

"May all those that come behind us, find us faithfull"

 

[mrdenny]

INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE should be what you are looking for.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com