I have a local db which has FOREIGN KEY constraints.
The live version of this websites DB, does not have any of these FOREIGN KEY constraints.
How can I "copy/paste", import/export ONLY the FOREIGN KEY constraints from one db to the other?
I do NOT want to copy any data, only the constraints.
Thanks
You could use this script I found at http://www.siusic.com/wphchen/how-to-script-out-all-the-foreign-keys-of-a-table-106.html (archive.org). Replace tablename1 and tablename2 with the list of tables you wish to get the foreign keys for.
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 
I needed to do something similar, where I needed the same foreign keys on multiple servers, except some had already been added. So I added a "IF NOT EXISTS" check to the beginning of the creation statements:
SELECT N' IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(''' + QUOTENAME(fk.name) + ''') AND parent_object_id = OBJECT_ID(''' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) +''') ) BEGIN ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name) -- get all the columns in the constraint table FROM sys.columns AS c INNER JOIN sys.foreign_key_columns AS fkc ON fkc.parent_column_id = c.column_id AND fkc.parent_object_id = c.[object_id] WHERE fkc.constraint_object_id = fk.[object_id] ORDER BY fkc.constraint_column_id FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name) + '(' + STUFF((SELECT ',' + QUOTENAME(c.name) -- get all the referenced columns FROM sys.columns AS c INNER JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = c.column_id AND fkc.referenced_object_id = c.[object_id] WHERE fkc.constraint_object_id = fk.[object_id] ORDER BY fkc.constraint_column_id FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ') END' FROM sys.foreign_keys AS fk INNER JOIN sys.tables AS rt -- referenced table ON fk.referenced_object_id = rt.[object_id] INNER JOIN sys.schemas AS rs ON rt.[schema_id] = rs.[schema_id] INNER JOIN sys.tables AS ct -- constraint table ON fk.parent_object_id = ct.[object_id] INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id] WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0; If you do not want the "IF NOT EXISTS" checks (they really shouldn't matter), just delete the top 5 lines and add "SELECT N' " right before the "BEGIN", like this:
SELECT N'BEGIN ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) -- I found the core of this query online somewhere and I've been modifying it for some time. Credit to them for putting most of it together...
