Create and attach script for a database prior to detaching it

If you are going to detach a database in SQL Server that you’re planning on attaching later save yourself some trouble and generate the attach script first.  Running the code below will generate a script to be used to attach your database at a later time (assuming that you keep the database files in the same location that they were in when you detached it).  Make sure you’re in the database you’re going to detach when you run this script

declare @dbname as sysname

declare @sql as varchar(1000)

declare @n as integer

declare @filename as sysname

set @dbname = db_name()

declare curs cursor for

select rtrim(filename) from sysfiles

open curs

fetch next from curs into @filename

set @n = 0

print '--ATTACH SCRIPT FOR ' + @dbname

print 'exec sp_attach_db @dbname = N''' + @dbname + ''','

while @@fetch_status = 0

begin

      set @n = @n + 1

      print '@filename' + cast(@n as varchar) + ' = ''' + @filename + ''','

      fetch next from curs into @filename

end

close curs

deallocate curs