help with Script to DROP and CREATE all Foreign Key constraints

[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

Apreciate any help on this. Thanks,

AL Almeida
Senior DBA

http://www.saxotech.com/

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

 

[ptheriault]

you could create a temp table like this to use to create your script.

DECLARE @tempFK TABLE (
PKTABLE_QUALIFIER varchar(64),
PKTABLE_OWNER varchar(64),
PKTABLE_NAME varchar(64),
PKCOLUMN_NAME varchar(64),
FKTABLE_QUALIFIER varchar(64),
FKTABLE_OWNER varchar(64),
FKTABLE_NAME varchar(64),
FKCOLUMN_NAME varchar(64),
KEY_SEQ int,
UPDATE_RULE int,
DELETE_RULE int,
FK_NAME varchar(64),
PK_NAME varchar(64),
DEFERRABILITY int)


INSERT INTO @tempFK exec sp_fkeys <table_name>

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[aalmeida]

well that is what I'm doing the problem is that I can't find the referenced table and column

AL Almeida
Senior DBA

http://www.ajmedia.net/

&quot;May all those that come behind us, find us faithfull&quot;

 

[ptheriault]

Did you run exec sp_fkeys <table_name> ?
That gives you the FKTable_NAME and FKCOLUMN_NAME

My point is use sp_fkeys to populate a temp table. Then use that temp table generate your script.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[aalmeida]

sorry I missed the sp_fkeys, I'll investigate that and post it back here

AL Almeida
Senior DBA

http://www.ajmedia.net/

&quot;May all those that come behind us, find us faithfull&quot;