DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to generate detach-attach script?

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'   

*/

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE