Execute the following SQL Server T-SQL script in Management Studio Query Editor to create and test a stored procedure for database detach and attach script generation.

-- SQL Server stored procedure - SQL cursor - SQL Server dynamic sql
-- SQL Server attach / detach
USE AdventureWorks;
Go
CREATE PROCEDURE DetachAttachScriptGenerator
                @Database SYSNAME  = '%'
AS
  BEGIN
    SET NOCOUNT  ON
    
    DECLARE  @SQL VARCHAR(MAX)
    
    SET @SQL = ''
    
    CREATE TABLE #SQLScript (
      Line VARCHAR(4000))
    
    DECLARE  @curDatabaseName CURSOR,
             @dbName          SYSNAME
    
    SET @curDatabaseName = CURSOR FOR SELECT NAME
                                      FROM   SYS.DATABASES
                                      WHERE  NAME NOT IN ('master','tempdb','model','msdb')
                                             AND NAME LIKE @Database
    
    OPEN @curDatabaseName
    
    FETCH NEXT FROM @curDatabaseName
    INTO @dbName
    
    WHILE (2 > 1)
      BEGIN
        SELECT @SQL = @SQL + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + '--' + @dbName + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
        
        BEGIN
          SELECT @SQL = @SQL + 'exec sp_detach_db @dbname = ''' + NAME + ''' ' + CHAR(13) + CHAR(10) + 'go' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
          FROM   SYS.DATABASES
          WHERE  NAME = @dbName
        END
        
        BEGIN
          SELECT @SQL = @SQL + 'exec sp_attach_db @dbname = ''' + @dbName + ''','
          
          INSERT INTO #SQLScript
          EXEC( 'select char(13) + '' @filename'' + cast(rowNumber as varchar(10))
          + '' = '''''' + filename + '''''''' +  case when rowNumber <>
          (select count(*) from sys.sysfiles) then '','' else '''' end
          from ( select filename, row_number() over (order by fileid)
          as rowNumber from ' + @dbname + '.sys.sysfiles ) as files')
          
          SELECT @SQL = @SQL + Line
          FROM   #SQLScript
          
          TRUNCATE TABLE #SQLScript
        END
        
        FETCH NEXT FROM @curDatabaseName
        INTO @dbName
        
        IF @@FETCH_STATUS <> 0
          BREAK
      END
    
    SELECT @SQL
  END
 
GO
 
-- Test sproc
-- Execute stored procedure
EXEC DetachAttachScriptGenerator
  @Database = 'AdventureWorks%'
 
/* Partial results
 
 
    --AdventureWorks   
    exec sp_detach_db @dbname = 'AdventureWorks'  
    go   
    exec sp_attach_db @dbname = 'AdventureWorks',  @filename1 =
    'C:\DATA\AdventureWorks_Data.mdf', 
    @filename2 = 'C:\DATA\AdventureWorks_Log.ldf'   
    --AdventureWorksDW  
     exec sp_detach_db @dbname = 'AdventureWorksDW'  
     go   
     exec sp_attach_db @dbname = 'AdventureWorksDW',
      @filename1 = 'C:\DATA\AdventureWorksDW_Data.mdf', 
      @filename2 = 'C:\DATA\AdventureWorksDW_Log.LDF'   

*/