Attach all databases

Run this proc to create the output to attach all databases
Copy the output to a new query and execute it




--Attach all databases to
--F:\mssql\data
--E:\mssql\data


set nocount on
declare @dbname sysname,@string nvarchar(600),@f1 varchar(100), @f2 varchar(100)
declare @d varchar(200),@len int,@totlen int,@x int
declare @parmdef  nvarchar(200)
declare dbcr cursor
 for  select [name] from sys.sysdatabases where [name] <> 'msdb' and [name] <> 'master' and [name] <> 'model' and [name] <> 'tempdb'
OPEN dbcr
FETCH next
    FROM  dbcr into   @dbname
WHILE (@@FETCH_status= 0)
BEGIN
     select @string = N'use ' + @dbname + ';  select @f1=[filename] from sysfiles where fileid = 1;'  
     set @parmdef =N'@f1 varchar(200) output'
     EXECUTE SP_EXECUTESQL @string,@parmdef,
             @f1=@f1 output
     select @string = N'use ' + @dbname + ';  select @f2=[filename] from sysfiles where fileid = 2;'  
     set @parmdef =N'@f2 varchar(200) output'
     EXECUTE SP_EXECUTESQL @string,@parmdef,
             @f2=@f2 output
   select @totlen = datalength (@f1)
   select @len = datalength (@f1) - charindex('\',reverse( @f1)) + 1
   select @len 
   select @x = @totlen - @len
   select @d = right(@f1,@x)   
   select @string =  'sp_attach_db @dbname=N' + ''''+@dbname+'''' + ',@filename1=N' +''''+ 'F:\mssql\data\'+ @d +''''
   select @totlen = datalength (@f2)
   select @len = datalength (@f2) - charindex('\',reverse( @f2)) + 1
   select @len 
   select @x = @totlen - @len
   select @d = right(@f2,@x)   
   select @string =  @string + ',@filename2=N' +''''+ 'E:\mssql\data\'+ @d + ''''     
   print @string
   print 'go'
 FETCH next
    FROM dbcr into @dbname
end
close dbcr
deallocate dbcr