Siusic Dot Com

I encountered a problem at work when I was requested to set up one way transactional replication between two databases. The subscribing database already have the tables of the replication and there were many tables in the subscribing database that have foreign keys referencing the replicating tables. So guess what? The initial push of the replication snap shot failed. Because the intial push of the publication snap shot was trying to drop the existing tables and recreate them.

Sounds an odd replication situation but I need to fullfill it anyway. So I wrote a script to drop the foreign keys that referencing the replicating tables and recreate those foreigns keys after the snap shot has been deliver. There may be other situation where you want to find all the foreign keys that reference certain tables and the following script may help.

The script has two part. The first part is to generate the drop statements to drop the foreigns keys that referece certain tables. The second part generates the statements to create the foreign keys. The script is writing on SQL server 2005 and it has limitation. It didn’t consider table schema and it doesn’t work on composite foreign keys. If anyone want a SQL 2000 verion or want one that is able to work on composite keys then let me know. I will see if I can help.

select 	'ALTER TABLE '+object_name(a.parent_object_id)+
	' DROP CONSTRAINT '+ a.name
from 	sys.foreign_keys a
     	join sys.foreign_key_columns b
                    on a.object_id=b.constraint_object_id
     	join sys.columns c
                    on b.constraint_column_id = c.column_id
	          and b.parent_object_id=c.object_id
     	join sys.columns d
                    on b.referenced_column_id = d.column_id
	          and b.referenced_object_id = d.object_id
where   object_name(b.referenced_object_id) in
	('tablename1','tablename2')
order   by c.name

select  'ALTER TABLE '+object_name(a.parent_object_id)+
	' ADD CONSTRAINT '+ a.name +
	' FOREIGN KEY (' + c.name + ') REFERENCES ' +
	object_name(b.referenced_object_id) +
	' (' + d.name + ')'
from 	sys.foreign_keys a
     	join sys.foreign_key_columns b
                  on a.object_id=b.constraint_object_id
     	join sys.columns c
                  on b.parent_column_id = c.column_id
	         and a.parent_object_id=c.object_id
     	join sys.columns d
                  on b.referenced_column_id = d.column_id
	        and a.referenced_object_id = d.object_id
where   object_name(b.referenced_object_id) in
	('tablename1','tablename2')
order by c.name

You should replace tablename with the name of the table you want to script foreign keys

Related Posts:

A Useful Script to Get The Disk Space Used by Every Table

VB Script to Rename All Files in a Folder

T-SQL Query to Get Database Size

Join to Parametrized Table Function - Cross Apply and Outer Apply in SQL Server 2005

Shell Script to Start Tomcat on Reboot Using Non-Root User