SQL Server - T-SQL script to extract out create statement of foreign keys

After a lot of thinking that what should I put in my first technical post, I zeroed in to this script which comes very handy to me at a times. 

The following T-SQL script help in extracting out "Create Statements" of all the foreign keys for a given database. This script can be very useful when we have to do a lot of data transfer (may it be for whole database or for several tables) from one database to another while having tables with lot of parent-child relationship in them. In such case we can extract out the foreign keys (using the below mentioned script), drop them, import the data and create the relationships back.

Or simply if you have to port all the  new foreign key relationships you have created lately on you local or dev database and want them to be created on some other database (may be other local or dev database etc..) for enhanced testing, in just few clicks.

declare @crlf char(2), @cmd varchar(max), @debug bit
set @debug = 1
set @cmd = ''
set @crlf = char(0x0d) + char(0x0a);

declare cur_fk cursor for
select 'If Exists (select 1 from sys.foreign_keys where name = ''' + k.name + ''' and parent_object_id = object_id(N''' + quotename(s1.name)+'.'+quotename(o1.name)+''')' + ' and referenced_object_id = object_id(N''' + quotename(s2.name)+'.'+quotename(o2.name)+''')' + ')' + @crlf +
'Alter Table '+ quotename(s1.name) + '.' + quotename(o1.name) + ' DROP constraint ' + quotename(k.name) + @crlf + 'go' + @crlf +
'Alter Table '+ quotename(s1.name) + '.' + quotename(o1.name) + ' WITH NOCHECK add constraint ' + quotename(k.name) + ' foreign key (' + c1.name + ') references ' + quotename(s2.name) + '.' + quotename(o2.name) + '(' + c2.name + ')' + @crlf + 'go' + @crlf +
'Alter Table '+ quotename(s1.name) + '.' + quotename(o1.name) + ' CHECK constraint ' + quotename(k.name) + @crlf + 'go' + @crlf
from sys.foreign_key_columns f join sys.objects o1 on o1.object_id = f.parent_object_id
join sys.schemas s1 on s1.schema_id = o1.schema_id
join sys.objects o2 on o2.object_id = f.referenced_object_id
join sys.schemas s2 on s2.schema_id = o2.schema_id
join sys.foreign_keys k on k.object_id = f.constraint_object_id
join sys.columns c1 on c1.object_id = o1.object_id and c1.column_id = f.parent_column_id
join sys.columns c2 on c2.object_id = o2.object_id and c2.column_id = f.referenced_column_id

open cur_fk

fetch next from cur_fk into @cmd

while @@fetch_status = 0
begin

if @debug = 1
print @cmd
else
Exec (@cmd)

fetch next from cur_fk into @cmd

end

close cur_fk
deallocate cur_fk

By including appropriate where clauses (like on o1.objectid to list out keys for a list of particular tables) to the main query (mentioned in cursor) in the above statement we can extract foreign keys for limited number of tables instead going in for whole database.

I hope this post will be helpful to some.

I will continue to put in other similar helpful and easy queries in coming days.