IF EXISTS 
	(SELECT name FROM sys.sysobjects WHERE name = 'USP_ATTACHDB')
	DROP PROCEDURE USP_ATTACHDB
GO

CREATE PROC USP_ATTACHDB
           @db SYSNAME  = NULL
AS
  SET nocount  ON
  SET concat_null_yields_null  OFF
  DECLARE  @cmd VARCHAR(1000)
  DECLARE  @a VARCHAR(2000)
  DECLARE  @Filecnt INT
  DECLARE  @cnt INT
  DECLARE  @sq CHAR(1)
  DECLARE  @dq CHAR(2)
  DECLARE  @TempFilename VARCHAR(1000)
  DECLARE  @TempFilename1 VARCHAR(1000)
  SET @sq = ''''
  SET @dq = ''''''
  SET @cnt = 1
  IF @db IS NOT NULL
    BEGIN
      CREATE TABLE #1 (
        fileid   INT,
        filename SYSNAME,
        name     SYSNAME)
      SET @cmd = 'Insert into #1 (fileid,filename,name) Select fileid,filename,name from '
                   + QUOTENAME(@db)
                   + '.dbo.sysfiles'
      EXEC( @cmd)
      SELECT @filecnt = MAX(fileid)
      FROM   #1
      WHILE @cnt <= @filecnt
        BEGIN
          SELECT @TempFileName = filename
          FROM   #1
          WHERE  fileid = @cnt
          SELECT @TempFileName = RTRIM(@TempFileName)
          SELECT @a = @a
                        + ','
                        + CHAR(13)
                        + CHAR(9)
          SELECT @a = @a
                        + '@filename'
                        + CONVERT(VARCHAR(2),@cnt)
                        + ' = '
                        + @sq
                        + @TempFilename
                        + @sq
          SET @cnt = @cnt
                       + 1
        END
      SELECT @a = 'EXEC sp_attach_db @dbname = '
                    + @sq
                    + @db
                    + @sq
                    + @a
      PRINT @a
    END
  ELSE
    BEGIN
      DECLARE db_cursor CURSOR  FOR
      SELECT   name
      FROM     sysdatabases
      WHERE    name NOT IN ('tempdb','master','msdb','model')
               AND dbid NOT IN (SELECT database_id
                                FROM   sys.databases
                                WHERE  state > 0)
      ORDER BY dbid
      OPEN db_cursor
      FETCH NEXT FROM db_cursor
      INTO @db
      WHILE @@FETCH_STATUS = 0
        BEGIN
          CREATE TABLE #2 (
            fileid   INT,
            filename SYSNAME,
            name     SYSNAME)
          SET @cmd = 'Insert into #2 (fileid,filename,name) select file_id, physical_name, name 
from sys.master_files where database_id = db_id('''+@db+''')'
          EXEC( @cmd)
          SELECT @filecnt = (select count(*) from sys.master_files where database_id = db_id(@db))
          FROM   #2
          WHILE @cnt <= @filecnt
            BEGIN
              SELECT @TempFileName = filename
              FROM   #2
              WHERE  fileid = @cnt
              SELECT @TempFileName = RTRIM(@TempFileName)
              SELECT @a = @a
                            + ', '
                            + CHAR(13)
                            + CHAR(9)
              SELECT @a = @a
                            + '@filename'
                            + CONVERT(VARCHAR(2),@cnt)
                            + ' = '
                            + @sq
                            + @TempFilename
                            + @sq
              SET @cnt = @cnt
                           + 1
            END
          SELECT @a = 'EXEC sp_attach_db @dbname = '
                        + @sq
                        + @db
                        + @sq
                        + @a
          PRINT @a
          PRINT 'GO'
          SELECT @a = ' '
          DROP TABLE #2
          SET @cnt = 1
          FETCH NEXT FROM db_cursor
          INTO @db
        END
      CLOSE db_cursor
      DEALLOCATE db_cursor
    END