CODEWITHSUNDEEP
sqlsql-server-2008
James Wilson
James Wilson

Reputation: 5150

SQL Server data migration fail

So we moved our SQL server to a new platform. During this move the primary and foreign keys were not transferred.

Is there a way I can script the keys from the old tables and add them to the new tables?

It doesn't look like they are going to redo it and just have us all the keys manually. Any speedy/safe way to do this is appreciated.

SQL Server 2008

Upvotes: 0

Views: 55

Answers (2)

bwperrin
bwperrin

Reputation: 692

Yup, you're going to love this. I have to drop and re-add FK's once in a while (like when I want to change an index that SQL hijacked for FK enforcement), so I wrote a view that will give me the drop and add statements:

select *, addqry = 'ALTER TABLE '+FKtable+' WITH ' + case when is_not_trusted = 1 then 'NO' else '' end + 'CHECK'
                    + ' ADD  CONSTRAINT ['+FK+'] FOREIGN KEY ('+FKcol+') '
                    + ' REFERENCES '+PKtable+' ('+PKcol+')'+' ON UPDATE '+onUpdate+' ON DELETE '+onDelete
                    + case when is_not_for_replication = 1 then ' NOT FOR REPLICATION' else '' end + ';'
                    + case when is_disabled = 1 then '  ALTER TABLE '+FKtable+' NOCHECK CONSTRAINT ['+FK+'];' else '' end
    ,dropqry = 'ALTER TABLE '+FKtable+' DROP ['+FK+'];'
from (
    select   PKtable        = object_schema_name(f.referenced_object_id)+'.'+object_name(f.referenced_object_id)
        ,PKtbl      = object_name(f.referenced_object_id)
        ,PKcol      = pc.name
        ,FKtable        = object_schema_name(f.parent_object_id)+'.'+object_name(f.parent_object_id)
        ,FKtbl      = object_name(f.parent_object_id)
        ,colseq     = fk.constraint_column_id
        ,FKcol      = fc.name
        ,FK     = object_name(f.object_id)
        ,onUpdate   = replace(f.update_referential_action_desc collate SQL_Latin1_General_CP1_CI_AS, '_', ' ')
        ,onDelete   = replace(f.delete_referential_action_desc collate SQL_Latin1_General_CP1_CI_AS, '_', ' ')
        ,f.is_disabled
        ,f.is_not_trusted
        ,f.is_not_for_replication
    from sys.foreign_key_columns as fk
        join sys.foreign_keys f on fk.constraint_object_id = f.object_id
        join sys.columns as fc on f.parent_object_id = fc.object_id and fk.parent_column_id = fc.column_id
        join sys.columns as pc on f.referenced_object_id = pc.object_id and fk.referenced_column_id = pc.column_id
) t

Upvotes: 1

Randy Minder
Randy Minder

Reputation: 48402

Since you're dealing with primary key and foreign keys, I would use a (free) tool like SQL Server Database Tools (SSDT). This is a Microsoft tool that integrates with Visual Studio (if you have it - you don't need it). It will synch a target schema with a source schema. It does essentially what a tool like Red Gat Schema Compare does, except it's free. And it works very well. Using a tool like this will ensure your entire schema got moved over, not just PKs and FKs.

Upvotes: 1

Related Questions