DBA - Generate Script To Detach All User Databases on SQL Server

Here is code that can be used to generate script to detach all the user databases on SQL Server. This can be helpful if during migration we need to detach all the SQL Server databases from one server and attach to other SQL Server.

After generating the script, Copy the output and execute in SSMS to detach all databases.

DECLARE @DatabaseName AS VARCHAR(500)

DECLARE DetachCur CURSOR FOR
  SELECT name
  FROM   MASTER.sys.databases
  WHERE  owner_sid > 1;
OPEN DetachCur
FETCH Next FROM DetachCur INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
  BEGIN
      PRINT 'sp_detach_db ' + @DatabaseName + CHAR(10)
            + ' GO' + CHAR(10) + 'Print ''Detach of '
            + @DatabaseName
            + ' database completed successfully'''
            + CHAR(10) + ' GO'

      FETCH Next FROM DetachCur INTO @DatabaseName
  END

CLOSE DetachCur
DEALLOCATE DetachCur