Detach All User Databases

USE [master]
GO
IF EXISTS ( SELECT *
 FROM sys.objects
 WHERE [object_id] = OBJECT_ID(N'[dbo].[spDetachAllUserDatabases]')
 AND type IN ( N'P', N'PC' ) ) 
 DROP PROCEDURE [dbo].[spDetachAllUserDatabases]
GO
CREATE PROCEDURE [dbo].[spDetachAllUserDatabases]
AS 
 BEGIN
  --Declare Variables
 DECLARE @DatabaseName VARCHAR(100)
 DECLARE @MinDatabaseID INT
 DECLARE @MaxDatabaseID INT
 DECLARE @SQL VARCHAR(4000)
    
  --Check for temporary table and drop it if it exists
 IF OBJECT_ID('tempDB.dbo.#Database') IS NOT NULL 
 DROP TABLE [#Database] ;
 
  --Create temporary table
 CREATE TABLE #Database
 (
 ID INT IDENTITY(1, 1),
 DatabaseName VARCHAR(100)
 )
 
  --Check for existing user databases
 IF EXISTS ( SELECT name
 FROM sys.databases
 WHERE database_id > 4
 AND name NOT IN ( 'SQLDBA', 'ReportServer',
 'ReportServerTempDB',
 'distribution' ) ) 
 BEGIN
  --Insert all database names into a temporary table
 INSERT INTO #Database ( DatabaseName )
 SELECT name
 FROM sys.databases
 WHERE database_id > 4
 AND name NOT IN ( 'SQLDBA', 'ReportServer',
 'ReportServerTempDB',
 'distribution' ) 
 
  --Set Variables for the detach database loop 
 SELECT @MinDatabaseID = MIN(ID),
 @MaxDatabaseID = MAX(ID)
 FROM #Database

  --Begin loop to detach databases
 WHILE @MinDatabaseID <= @MaxDatabaseID
 BEGIN
  --Get DatabaseName
 SELECT @DatabaseName = DatabaseName
 FROM #Database
 WHERE ID = @MinDatabaseID
 
  --Build Detach Database Command
 SET @SQL = 'EXEC sp_detach_db ' + '''' + @DatabaseName
 + '''' + ';'
        
  --Try Catch block to execute SQL and handle errors            
 BEGIN TRY
  --Detach Database
 EXEC ( @SQL
 )
 PRINT 'Detached ' + @DatabaseName
 END TRY
 BEGIN CATCH
 SELECT @DatabaseName,
 message_id,
 severity,
 [text],
 @SQL
 FROM sys.messages
 WHERE message_id = @@ERROR
 AND language_id = 1033 --British English
 END CATCH
        
  --Get the next DatabaseName ID
 SET @MinDatabaseID = @MinDatabaseID + 1    
  --End Loop
 END
 END
 END

GO