--批量分离数据库
declare @SqlStr varchar(8000)
select @SqlStr=''
select @SqlStr=@SqlStr+'EXEC sp_detach_db ['+[name]+']'+CHAR(10)
from master..sysdatabases where dbid>=7
Exec(@SqlStr)
--批量附加数据库
/*
准备工作:
--假设数据库全部在以下路径中:C:/Test,那么在CMD窗口中先进入目录C:/Test,再输入以下命令生成数据库名列表
dir/b/o/s *.mdf >a1.txt
dir/b/o/s *.ldf >a2.txt
dir/b/o *.mdf >a3.txt
dir/b/o *.ldf >a4.txt
--人手分别在a1.txt,a2.txt,a3.txt,a4.txt第一行加上c1,c2,c3,c4
*/
--测试数据
create table table1(c1 varchar(100),c2 varchar(100),c3 varchar(100),c4 varchar(100))
insert table1(c1) select c1 from opendatasource('microsoft.jet.oledb.4.0','Text;hdr=yes;database=C:/Test/')...a1#txt
Update table1 set c2 = a2.c2 from (select c2 from opendatasource('microsoft.jet.oledb.4.0','Text;hdr=yes;database=C:/Test/')...a2#txt) a2
Update table1 set c3 = a3.c3 from (select c3 from opendatasource('microsoft.jet.oledb.4.0','Text;hdr=yes;database=C:/Test/')...a3#txt) a3
Update table1 set c4 = a4.c4 from (select c4 from opendatasource('microsoft.jet.oledb.4.0','Text;hdr=yes;database=C:/Test/')...a4#txt) a4
--查询(假设附加后的数据名为符号_前面的名字)
declare @SqlStr varchar(8000)
select @SqlStr=''
select @SqlStr=@SqlStr+'EXEC sp_attach_db @dbname = '''+substring(c3,1,charindex('_',c3)-1)+''',@filename1 ='''+c1+''',@filename2 ='''+c2+''''+CHAR(10)
from a..table1
exec(@SqlStr)
--删除测试数据
drop table table1
/*
其实如果只是暂时分离这60个数据库,然后原封不动地附加进去的话,其实还有更快的办法把这60个数:分离前先据库名称的列表及其路径导到临时表,这样以上的准备工作和测试数据就可省了:)
--导出数据库名称的列表及其路径到临时表
if object_id('table1') is not null drop table table1
select filename as filename1,replace(filename,'mdf','ldf') as filename2, name into table1 from master..sysdatabases
where dbid>=7
*/