环境为 sqlserver 2000
现在有a和b两个库
a库中有数据,b库为新建的数据库
我想要将a库中的,表,结构,存储过程,视图,自定义函数等等等(不含数据)复制到库b中
目前,我能想到的思路是
1,delete from tablename 挨个儿删除,但是这样工作量非常大,另外涉及到外键的地方也很麻烦
2,truncate table tablename 这个虽然和上面的delete类似,除了工作量更大一下之外,速度和其他方面比delete要好,但是,
问题是涉及到外键的地方,就报错了,哎...因为数据库比较复杂,生怕弄错了
3,所谓导出sql,这个我倒是导出来了,如何导进去呢? 倒进去还是有几个表要修复.....
求查看表外键,关闭和打开外键相关代码
求思路,求方法,求步骤
------解决方案--------------------
- SQL code
--查询外键约束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 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 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 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 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)tbgroup by FK_Name,Parent_Tab_Name,Referenced_Tab_Name--1.查出所有有外键的表--2.删除外键表中的数据--3.删除主键表中数据--4.备份 还原
------解决方案--------------------