Generate CREATE DATABASE FOR ATTACH for all databases on server

--------------------------------------------------------------------------------------------------
--Create Database For attach. Run this on the server it will generate statements to reattach the--
--Databases. This is good for uninstalling sql and reinstalls without having to restore the --
--databases.--
--------------------------------------------------------------------------------------------------
declare @dbname varchar(255)
DECLARE @sql varchar(8000)
DECLARE @file varchar(255)
DECLARE @size varchar(15)
DECLARE @growth varchar(15)
DECLARE @name varchar(255)
DECLARE @group varchar(255)
DECLARE @prevgroup varchar(255)


DECLARE dbs CURSOR
READ_ONLY
FOR select name from master..sysdatabases order by name


OPEN dbs

FETCH NEXT FROM dbs INTO @dbname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

SET NOCOUNT ON

/******************************************************
 Drop and recreate the temp table we'll use to 
 temporarily store table data
*/
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = N'DetachData')
 DROP TABLE [tempdb].[dbo].[DetachData]

CREATE TABLE [tempdb].[dbo].[DetachData] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[File] [varchar] (255) NOT NULL ,
[Size] [varchar] (15) NOT NULL ,
[Growth] [varchar] (15) NOT NULL ,
[Name] [varchar] (255) NOT NULL ,
[Group] [varchar] (255) NULL ,
[FileType] [char] (1) NOT NULL
) ON [PRIMARY]

/*******************************************************//* This will get the data file(s)
*/
SET @sql = 'INSERT INTO tempdb..DetachData ([File],[Size],Growth,[Name],[Group],FileType) '
SET @sql = @sql + '(SELECT sf.filename, '
SET @sql = @sql + 'CASE WHEN sf.size < 129 THEN CONVERT(nvarchar(15),(sf.size*8)/1024) ELSE CONVERT(nvarchar(15),(sf.size*8)/1024) + 1 END size, '
SET @sql = @sql + 'CASE sf.status & 0x100000 WHEN 0x100000 THENCONVERT(nvarchar(3), sf.growth) + N''%'' ELSE CONVERT(nvarchar(15), sf.growth * 8) + N'' KB'' END growth, '
SET @sql = @sql + 'sf.name, '
SET @sql = @sql + 'sfg.groupname, '
SET @sql = @sql + '''D'' AS filetype '
SET @sql = @sql + 'FROM ' + @dbname + '..sysfiles sf INNER JOIN ' + @dbname + '..sysfilegroups sfg ON sf.groupid = sfg.groupid) '
SET @sql = @sql + 'ORDER BY sfg.groupid,sf.fileid'

EXECUTE (@sql)
SET @sql = ''

/*******************************************************//* This will get the log file(s)
*/
SET @sql = 'INSERT INTO tempdb..DetachData ([File], [Size], Growth, [Name], FileType) '
SET @sql = @sql + 'SELECT sf.filename, '
SET @sql = @sql + 'CASE WHEN sf.size < 129 THEN CONVERT(nvarchar(15),(sf.size*8)/1024) ELSE CONVERT(nvarchar(15),(sf.size*8)/1024) + 1 END size, '
SET @sql = @sql + 'CASE sf.status & 0x100000 WHEN 0x100000 THENCONVERT(nvarchar(3), sf.growth) + N''%'' ELSE CONVERT(nvarchar(15), sf.growth * 8) + N'' KB'' END growth, '
SET @sql = @sql + 'sf.name, '
SET @sql = @sql + '''L'' AS filetype '
SET @sql = @sql + 'FROM ' + @dbname + '..sysfiles sf '
SET @sql = @sql + 'WHERE groupid = 0'

EXECUTE (@sql)
SET @sql = ''

/******************************************************
 Dynamically create the statement by looping through
 the temp table we've created
*/
SET @sql = 'CREATE DATABASE ' + RTRIM(@dbname) + ' ON PRIMARY '

DECLARE DetachData CURSOR FOR
SELECT [File], [Size], [Growth], [Name], [Group]
FROM tempdb..DetachData
WHERE FileType = 'D'

OPEN DetachData
FETCH NEXT FROM DetachData INTO @file, @size, @growth, @name, @group
WHILE @@FETCH_STATUS = 0
  BEGIN
IF RTRIM(@group) = 'PRIMARY'
    SET @sql = @sql
   ELSE IF @group != @prevgroup
           SET @sql = @sql +  ' FILEGROUP ' + @group + ' '

  SET @prevgroup = @group

  SET @sql = @sql + '('
      SET @sql = @sql + 'NAME = ''' + RTRIM(@name) + ''','
  SET @sql = @sql + 'FILENAME = ''' + RTRIM(@file) + ''','
  SET @sql = @sql + 'SIZE = ' + @size + ','
   SET @sql = @sql + 'FILEGROWTH = ' + @growth
SET @sql = @sql + ')'
      FETCH NEXT FROM DetachData INTO @file, @size, @growth, @name, @group
    
      IF @@FETCH_STATUS = 0
        SET @sql = @sql + ','
  END

CLOSE DetachData
DEALLOCATE DetachData

SET @sql = @sql + ' LOG ON '

DECLARE DetachData CURSOR FOR
SELECT [File], [Size], [Growth], [Name], [Group]
FROM tempdb..DetachData
WHERE FileType = 'L'

OPEN DetachData
FETCH NEXT FROM DetachData INTO @file, @size, @growth, @name, @group
WHILE @@FETCH_STATUS = 0
  BEGIN
  
  SET @sql = @sql + '('
  SET @sql = @sql + 'NAME = ''' + RTRIM(@name) + ''','
  SET @sql = @sql + 'FILENAME = ''' + RTRIM(@file) + ''','
  SET @sql = @sql + 'SIZE = ' + @size + ','
   SET @sql = @sql + 'FILEGROWTH = ' + @growth
  SET @sql = @sql + ')'
      FETCH NEXT FROM DetachData INTO @file, @size, @growth, @name, @group
    
      IF @@FETCH_STATUS = 0
        SET @sql = @sql + ','
  END

CLOSE DetachData
DEALLOCATE DetachData

SET @sql = @sql + ' FOR ATTACH '

/******************************************************
 Finally, print the statement to the screen
*/
PRINT @sql

/******************************************************
 Drop the temp table
*/
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = N'DetachData')
 DROP TABLE [tempdb].[dbo].[DetachData]

END
FETCH NEXT FROM dbs INTO @dbname
END

CLOSE dbs
DEALLOCATE dbs
GO