SQL 2000批量分离与附加数据库

SQL 2000批量分离与附加数据库【len&odin】

--请武狼把此功能加入到Five_Wolf里面。3Q！感谢狼。

--批量分离数据库

declare @dbname varchar(2000)

declare @sql varchar(2000)

declare len_cur1 cursor for select db_name(dbid) as dbname from master..sysaltfiles

where fileid=1 and db_name(dbid) not in ('master','tempdb','model','msdb','pubs','Northwind')

open len_cur1

fetch next from len_cur1 into @dbname

while @@fetch_status = 0

begin

set @sql='sp_detach_db'+''''+@dbname+''''

exec (@sql)

--print (@sql)

fetch next from len_cur1 into @dbname

end

close len_cur1

deallocate len_cur1

--批量附加指定目录数据库存储过程

if object_id('attach_db_pro') is not null

drop procedure attach_db_pro

go

create procedure attach_db_pro

@dir nvarchar(520)

as

declare @dbname varchar(20)

declare @filename nvarchar(520)

declare @name sysname

declare @temp_db_name varchar(20)

declare @i int

declare @sqlq varchar(2000)

declare @sql varchar(2000)

select cast(space(100) as varchar) as dbname,0 as depth,0 as filee into #tmp from master..sysaltfiles where 1=2

set @sqlq='insert into #tmp exec xp_dirtree'''+@dir+'\'''+',1,1 '

exec (@sqlq)

select case when right(left(dbname,len(dbname)-4),4)='_LOG' then left(dbname,len(dbname)-8)

when right(left(dbname,len(dbname)-4),5)='_DATA' then left(dbname,len(dbname)-9) else left(dbname,len(dbname)-4) end as dbname, left(dbname,len(dbname)-4) as name,@dir+dbname filename into #lentmp from #tmp where filee=1

ORDER BY dbname

declare my_cursor cursor

for

select dbname,name,filename from #lentmp

where dbname not in (select db_name(dbid) from master..sysaltfiles)

open my_cursor

fetch next from my_cursor into @dbname,@name,@filename

while @@fetch_status = 0

begin

set @temp_db_name = @dbname

set @i = 1

set @sql='exec sp_attach_db @dbname ='''+@dbname+''',@filename'+ convert(varchar,@i) + ' =''' + @dir + '\' + rtrim(@name) + rtrim(right(@filename,4))+''''

fetch next from my_cursor into @dbname,@name,@filename

while @@fetch_status = 0 and @temp_db_name = @dbname

begin

set @i = @i + 1

set @sql=+@sql+',@filename' + convert(varchar,@i) + ' = ''' + @dir + '\'+rtrim(@name)+rtrim(right(@filename,4))+''''

exec (@sql)

fetch next from my_cursor into @dbname,@name,@filename

end

end

close my_cursor

deallocate my_cursor

drop table #tmp

drop table #lentmp

go

--批量附加数据库使用例子：

exec attach_db_pro 'E:\Data'

请武狼把此功能加入到Five_Wolf里面。3Q！感谢狼。