当我们要对两个数据库的相关数据进行完全同步的时候,我们首先是先删除数据,然后再添加数据,这样保证所有数据的一致性,
那么假设现在我们需要从源数据库中的表A同步到目标数据库中的表A,脚本如下
1.delete targetDBName.dbo.TBA
2.insert into targetDBName.dbo.TBA select * from sourceDBName.dbo.TBA (动态字段可以查询syscolumns表来实现)
那么在执行第一句脚本的时候,就有可能报错,因为TBA有可能存在外键关联。
在这种的情况下,
删除操作:先删子表在删主表
添加操作:先添主表在添子表
因此我们需要对TBA的所有外键进行排查,脚本如下:
--
查询外键约束
select FK_Name as [ 外键名 ] ,Parent_Tab_Name as [ 外键表 ] ,
[ 外键列 ] = stuff (( select ' , ' + [ Parent_Col_Name ] from (
select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
from sys.foreign_keys FK
inner join sys.foreign_key_columns Col on FK. Object_ID = Col.constraint_object_id
inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab. Object_ID and Parent_Tab.TYPE = ' U '
inner join sys.columns Parent_Col on Parent_Tab. Object_ID = Parent_Col. object_id
and Col.parent_column_id = Parent_Col.column_id
inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab. Object_ID
select FK_Name as [ 外键名 ] ,Parent_Tab_Name as [ 外键表 ] ,
[ 外键列 ] = stuff (( select ' , ' + [ Parent_Col_Name ] from (
select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
from sys.foreign_keys FK
inner join sys.foreign_key_columns Col on FK. Object_ID = Col.constraint_object_id
inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab. Object_ID and Parent_Tab.TYPE = ' U '
inner join sys.columns Parent_Col on Parent_Tab. Object_ID = Parent_Col. object_id
and Col.parent_column_id = Parent_Col.column_id
inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab. Object_ID
and
Referenced_Tab.TYPE
=
'
U
'
inner join sys.columns Referenced_Col on Referenced_Tab. Object_ID = Referenced_Col. object_id
and Col.referenced_column_id = Referenced_Col.column_id
)t where FK_Name = tb.FK_Name and Parent_Tab_Name = tb.Parent_Tab_Name
inner join sys.columns Referenced_Col on Referenced_Tab. Object_ID = Referenced_Col. object_id
and Col.referenced_column_id = Referenced_Col.column_id
)t where FK_Name = tb.FK_Name and Parent_Tab_Name = tb.Parent_Tab_Name
and
Referenced_Tab_Name
=
tb.Referenced_Tab_Name
for
xml path(
''
)),
1
,
1
,
''
),
Referenced_Tab_Name as [ 主键表 ] ,
[ 主键列 ] = stuff (( select ' , ' + [ Referenced_Col_Name ] from (
select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
from sys.foreign_keys FK
inner join sys.foreign_key_columns Col on FK. Object_ID = Col.constraint_object_id
inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab. Object_ID and Parent_Tab.TYPE = ' U '
inner join sys.columns Parent_Col on Parent_Tab. Object_ID = Parent_Col. object_id
and Col.parent_column_id = Parent_Col.column_id
inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab. Object_ID
Referenced_Tab_Name as [ 主键表 ] ,
[ 主键列 ] = stuff (( select ' , ' + [ Referenced_Col_Name ] from (
select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
from sys.foreign_keys FK
inner join sys.foreign_key_columns Col on FK. Object_ID = Col.constraint_object_id
inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab. Object_ID and Parent_Tab.TYPE = ' U '
inner join sys.columns Parent_Col on Parent_Tab. Object_ID = Parent_Col. object_id
and Col.parent_column_id = Parent_Col.column_id
inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab. Object_ID
and
Referenced_Tab.TYPE
=
'
U
'
inner join sys.columns Referenced_Col on Referenced_Tab. Object_ID = Referenced_Col. object_id
and Col.referenced_column_id = Referenced_Col.column_id
)t where FK_Name = tb.FK_Name and Parent_Tab_Name = tb.Parent_Tab_Name
inner join sys.columns Referenced_Col on Referenced_Tab. Object_ID = Referenced_Col. object_id
and Col.referenced_column_id = Referenced_Col.column_id
)t where FK_Name = tb.FK_Name and Parent_Tab_Name = tb.Parent_Tab_Name
and
Referenced_Tab_Name
=
tb.Referenced_Tab_Name
for
xml path(
''
)),
1
,
1
,
''
)
-- as [外键列]
from (
select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
from sys.foreign_keys FK
inner join sys.foreign_key_columns Col on FK. Object_ID = Col.constraint_object_id
inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab. Object_ID and Parent_Tab.TYPE = ' U '
inner join sys.columns Parent_Col on Parent_Tab. Object_ID = Parent_Col. object_id
and Col.parent_column_id = Parent_Col.column_id
inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab. Object_ID
-- as [外键列]
from (
select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
from sys.foreign_keys FK
inner join sys.foreign_key_columns Col on FK. Object_ID = Col.constraint_object_id
inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab. Object_ID and Parent_Tab.TYPE = ' U '
inner join sys.columns Parent_Col on Parent_Tab. Object_ID = Parent_Col. object_id
and Col.parent_column_id = Parent_Col.column_id
inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab. Object_ID
and
Referenced_Tab.TYPE
=
'
U
'
inner join sys.columns Referenced_Col on Referenced_Tab. Object_ID = Referenced_Col. object_id
and Col.referenced_column_id = Referenced_Col.column_id
)tb
-- WHERE tb.Parent_Tab_Name='PB_UserRole' OR tb.Referenced_Tab_Name='PB_UserRole'
group by FK_Name,Parent_Tab_Name,Referenced_Tab_Name
inner join sys.columns Referenced_Col on Referenced_Tab. Object_ID = Referenced_Col. object_id
and Col.referenced_column_id = Referenced_Col.column_id
)tb
-- WHERE tb.Parent_Tab_Name='PB_UserRole' OR tb.Referenced_Tab_Name='PB_UserRole'
group by FK_Name,Parent_Tab_Name,Referenced_Tab_Name