Detach User Databases

I started side by side upgrading SQL 2000 to SQL 2005, at that time I need to detach all the user databases from the source server and need to attach the same in destination server. Source server has almost 80 user databases and I feel bored to detach all the database one by one which is time consuming too.. Hence I thought of writing a script which will help me to detach the user databases in a single stretch. I know that someone will face this situation, hence I’m posting my script here which will help you!!

Script:

/*
Script to detach all user databases
Written by Vidhya Sagar
www.sql-articles.com
*/
SET NOCOUNT ON
DECLARE @dbname AS VARCHAR(80)
DECLARE @server_name AS VARCHAR(20)
SELECT @server_name = @@SERVERNAME
DECLARE rs_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('model','master','msdb','tempdb','distribution','repldata')
OPEN rs_cursor
FETCH NEXT FROM rs_cursor INTO @dbname
IF @@FETCH_STATUS <> 0
PRINT 'No User databases found!!!'
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @exec VARCHAR(8000)
SET @exec= 'DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''''
SELECT @spidstr=coalesce(@spidstr,'','' )+''kill ''+convert(varchar, spid)+ ''; ''
FROM master..sysprocesses WHERE dbid=db_id('''+@dbname+''')
IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id('''+@dbname+''')
END'+CHAR(10)+';'+CHAR(10)+
'Exec sp_detach_db ' + @dbname
EXEC (@exec)
PRINT 'Detach of ' + UPPER(@dbname) + ' database successfully completed'
PRINT ''
FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
DEALLOCATE rs_cursor
PRINT ' '
PRINT UPPER(@server_name) + '--> All User databases successfully detached'

Usage:

Copy the above script and execute it, this will automatically detach all the user databases and you will receive the output as above!!

Output:

Detach of NORTHWIND database successfully completed

Detach of PUBS database successfully completed

SAGARSYSSQL2K–> All User databases successfully detached

If you have any concurrence or discussion in the script above, lets discuss on SQL-Articles Forums section.