declare @B varchar(50) --新库名称
set @B='xinku'--新的数据库名称
declare @i int
set @i=0
declare @j int
select @j=count(name) from sysobjects where xtype='u' and name<>'dtproperties'
declare @a varchar(50)
declare 游标 scroll cursor
for
select name from sysobjects where xtype='u' and name<>'dtproperties'
open 游标
fetch first from 游标
declare @t table(id int identity(1,1),name varchar(50))
--下面的是数据库中的含有自增字段的表,可根据实际情况自己修改
insert into @t(name) select 'BiaoZhun' union select 'BiaoZhun1' union select 'CaiPinYongLiao' union
select 'CangKu' union select 'ChuKu' union select 'DaYinJiGuanLi' union select 'DengLuMingXi' union
select 'GongYingShang' union select 'JiaoBanJiLu' union select 'JiLiangDanWeiBiao' union select 'KuCun' union
select 'KuCunZhangHao' union select 'TaiWei' union select 'TuiCai' union select 'YuanCaiLiao' union
select 'zhuantaijilu' union select 'YuanCaiLiaoLeiBie' union select 'TuiCaiMingXi'
while @@FETCH_STATUS=0
begin
set @i=@i+1
if(@i>=@j)
break
fetch next from 游标 into @a
select * from @t where name =''+@a+''
if @@rowcount=0
begin
exec ('insert into '+@B+'..'+@a+' select * from '+@a)
end
else
begin
declare @lie varchar(200)
set @lie='bianhao'
--需要修改自增字段的
declare 游标1 scroll cursor
for
select name from syscolumns where id=object_id(''+@a+'')
open 游标1
declare @lie1 varchar(50)
fetch first from 游标1
while @@FETCH_STATUS=0
begin
fetch next from 游标1 into @lie1
set @lie=@lie+','+@lie1
end
set @lie=substring(@lie,1,len(@lie)-charindex(',', reverse(@lie)))
fetch first from 游标1
close 游标1
deallocate 游标1
exec ('set identity_insert '+@B+'..'+@a+' on insert into '+@B+'..'+@a+'('+@lie+') select * from '+@a+' set identity_insert '+@B+'..'+@a+' off')
end
end
close 游标
deallocate 游标
上面的是创建好数据库'xinku'之后并且表已经创建好
估计可以换个方法select * into xinku..table from table
没有测试过，可以自己测试一下
游标处理数据导入导出的问题（包括含有自增字段的表）
