Script To Detach All User Databases on SQL Server

Here is a query to generate a script to detach all user databases on your SQL Server.

Once you have run the query, copy the output and run it in SSMS to detach all your user databases. This can be really handy during migration activity when you have lot of databases to detach and move around.

DECLARE @dbName varchar(255); -- To store database name 
DECLARE DBCURSOR CURSOR FOR
	SELECT name 
	FROM sys.databases 
	WHERE len(owner_sid)>1; -- All user databases

OPEN DBCURSOR
FETCH Next from DBCURSOR INTO @dbName
WHILE @@FETCH_STATUS = 0 
BEGIN
	PRINT 'EXEC sp_detach_db ' + @dbName + CHAR(10) -- CHAR(10) for newline 
	+ 'GO' + CHAR(10) 
	+ 'Print ''Detach of ' + @dbName + ' database completed successfully''' 
	+ CHAR(10) + 'GO'
	FETCH NEXT FROM DBCURSOR INTO @dbName
END

CLOSE DBCURSOR    
DEALLOCATE DBCURSOR

Sample output:

I have two databases TestDB and Sample. Running the above query gives the following output.

EXEC sp_detach_db TestDB
GO
Print 'Detach of TestDB database completed successfully'
GO
EXEC sp_detach_db Sample
GO
Print 'Detach of Sample database completed successfully'
GO