Drop all databases from server

I have a server (SQL Server 2005) with more than 300 databases. I don't want to right-click one by one and select Delete.

How can I delete all databases easily?

Solution 1:

You can do this through the SSMS GUI. Select the Databases node then F7 to bring up Object Explorer Details, Select all databases that you want to delete, Hit "Delete" and select the "Close Existing Connections" and "Continue after error" options.

Alternatively through TSQL you can do

EXEC sp_MSforeachdb '
IF DB_ID(''?'') > 4
BEGIN
EXEC(''
ALTER DATABASE [?] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [?]
'')
END'

Solution 2:

And here is my solution for the same problem:

-- drops all user databases
DECLARE @command nvarchar(max)
SET @command = ''

SELECT  @command = @command
+ 'ALTER DATABASE [' + [name] + ']  SET single_user with rollback immediate;'+CHAR(13)+CHAR(10)
+ 'DROP DATABASE [' + [name] +'];'+CHAR(13)+CHAR(10)
FROM  [master].[sys].[databases] 
 where [name] not in ( 'master', 'model', 'msdb', 'tempdb');

SELECT @command
EXECUTE sp_executesql @command

Solution 3:

This will kill all connections, and delete all databases not in the list:

('master' ,'tempdb' ,'model' ,'msdb' ,'ReportServer' ,'ReportServerTempDB')

use [master]

DECLARE 
@DATABASENAME nVARCHAR(20)

DECLARE
@TABLE TABLE
(NAME nVARCHAR(50))

Declare @SQL nvarchar(100)

INSERT INTO @TABLE

SELECT 
name 
FROM sys.databases 
WHERE name not in 
    ('master'
    ,'tempdb'
    ,'model'
    ,'msdb'
    ,'ReportServer'
    ,'ReportServerTempDB')


while (select COUNT(*) from @table) > 0

begin

select @DATABASENAME = (select top 1 (name) from @TABLE)

DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses 
WHERE dbid = db_id(@DATABASENAME)

EXEC(@kill);

set @SQL = 'drop database ' + @DATABASENAME

exec sp_executesql @SQL, N'@databasename nvarchar(50)', @databasename; 

print @databasename + ' has been deleted'

delete from @TABLE where NAME = @DATABASENAME

end

Solution 4:

Here is my PowerShell script version. DropAllDatabases.ps1

$sqlCmdPath="C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\sqlcmd.exe"
& $sqlCmdPath -S localhost -Q "EXEC sp_MSforeachdb 'IF DB_ID(''?'') > 4 BEGIN PRINT ''?'' ALTER DATABASE [?] SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE [?] END'"

Note: You may need to update the $sqlCmdPath to your version of SQL.

Solution 5:

The safe way of deleting (dropping) all databases in MS-SQL using T-SQL is to exclude all system databases, and any other database(s) that you want to keep, and special databases such as Data Warehouse database "DW", Report server database.

Excluding all the databases that we want to keep including all system databases will make it safe to delete just everything else that we don't want to keep.

For example:

use master
go
declare @dbnames nvarchar(max)
declare @statement nvarchar(max)
set @dbnames = ''
set @statement = ''
select @dbnames = @dbnames + ',[' + name + ']' from sys.databases 
    where name 
    NOT IN ('master','model','msdb','tempdb') 
    AND name NOT LIKE '%AdventureWorks%' -- Database to keep 
    AND name NOT LIKE '%DW%' -- Data warehouse database
    AND name NOT LIKE '%ReportServer%' -- Report server database
if len(@dbnames) = 0
    begin
    print 'no databases to drop'
    end
else
    begin
    set @statement = 'drop database ' + substring(@dbnames, 2, len(@dbnames))
    print @statement
    exec sp_executesql @statement
    end
go