批量附加数据库的一种方法

--作者：chenjing957 
--http://www.windbi.com/showtopic.aspx?forumid=6&topicid=245&go=prev
--使用方法：在重装系统前，运行这个，把生成的SQL语句保存起来
--　　　　　重装系统后再运行生成的那个文件，便可实现批量附加数据库

--目前只适合SQL2005，SQL2000的不行，还正在研究。研究出来会放上来2008-12-11
--批量附加数据库备份例子：
--exec attach_db_pro 'c:\program files\microsoft sql server\data'

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 my_cursor cursor
for 
    select  db_name(dbid) dbname,name,filename 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
          print 'exec sp_attach_db @db_name = ' + @dbname + ',@filename' + convert(varchar,@i) + ' = ''' + @dir + '\' + @name + 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
                  print ',@filename' + convert(varchar,@i) + ' = ''' + @dir + '\'+@name+right(@filename,4)+''''
                  fetch next from my_cursor into @dbname,@name,@filename
          end
          print ''
    end
close my_cursor
deallocate my_cursor
go