Detach All User Databases

Vidhya Sagar, May 06, 2008

Occasionally, it may be useful to Detach all User Databases from a SQL Server instance.
This script may be helpful when it is necessary to detach all the user databases in a single in a short time; all user connections will be terminated so the databases can be detached.

Script

-- Script to Detach All User Databases
-- Written by Vidhya Sagar
 
SET NOCOUNT ON
 
DECLARE 
   @dbName     varchar(80),
   @ServerName varchar(20)
 
SELECT @ServerName = @@servername
 
DECLARE dbCursor CURSOR FOR 
   SELECT name 
   FROM master.dbo.sysdatabases 
   WHERE name NOT IN ( 'model', 'master', 'msdb', 'tempdb', 'distribution', 'repldata' )
 
OPEN dbCursor 
 
FETCH NEXT FROM dbCursor INTO @dbName
 
IF ( @@FETCH_STATUS <> 0 )
   PRINT 'No User databases found!!!'
   
WHILE ( @@FETCH_STATUS = 0 )
   BEGIN
      DECLARE @SQLStr varchar(8000) 
      SET @SQLStr = 
        'DECLARE 
            @SPIDStr    varchar(8000),
            @ConnKilled smallint
         SELECT 
            @ConnKilled = 0
            @SPIDStr    = ''''
         SELECT @SPIDStr = coalesce( @SPIDStr, '', '' ) + ''KILL '' + convert( varchar, spid ) + ''; ''
         FROM master.dbo.sysprocesses 
         WHERE dbid = db_id( ''' + @dbName + ''' )
         IF LEN( @SPIDStr ) > 0 
            BEGIN
               EXECUTE( @SPIDStr )
               SELECT @ConnKilled = COUNT(1)
               FROM master..sysprocesses 
               WHERE dbid = db_id( ''' + @dbName + ''' )
            END' + char(10) + ';' + char(10) + 
        'EXECUTE sp_detach_db ' + @dbName
      EXECUTE ( @SQLStr )
      PRINT 'Detach of ' + upper( @dbName ) + ' Database Successfully Completed'
      PRINT ''
      FETCH NEXT FROM dbCursor INTO @dbName
   END
   
CLOSE dbCursor
DEALLOCATE dbCursor
 
PRINT ' '
PRINT upper( @ServerName ) + ' --> All User Databases Successfully Detached'

Output

Detach of NORTHWIND Database Successfully Completed
Detach of PUBS Database Successfully Completed
SAGARSYS\SQL2K --> All User databases Successfully Detached

Usage
Execute the above script.
All User Databases will be detached, with output similar to that indicated above.

CAUTION
Since all users connections will be terminated, there could be data loss from incomplete transactions.

Return to Top

___________________________________________________________________________________________________________________
Page Created By: Arnie Rowland, May 06, 2008