CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Moslem7026
Moslem7026

Reputation: 3338

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?

Upvotes: 60

Views: 92726

Answers (10)

lava
lava

Reputation: 7441

enter image description here

There are many best solution mentioned below and above.You can also use this solution also .

DECLARE @Counter INT , @MaxId INT, 
        @CountryName VARCHAR(100),@sql varchar(100)
SELECT @Counter = min(database_id) , @MaxId = max(database_id) 
FROM sys.databases 
 
WHILE(@Counter IS NOT NULL
      AND @Counter <= @MaxId)
BEGIN
 --begin try
if exists(select name from (select name from sys.databases where database_id=@Counter) as s where s.name not in ('master','tempdb','model','msdb','ReportServer$SQLEXPRESSTempDB','ReportServer$SQLEXPRESS')) 
begin
  SELECT @CountryName = name
   FROM sys.databases where  database_id=@Counter 
    
   PRINT CONVERT(VARCHAR,@Counter) + '. Database name is ' + @CountryName  ;
     SET @SQL = 'DROP DATABASE ' + @CountryName 
    PRINT @SQL
    EXEC (@SQL)
print 'success'
end
else

begin 
print 'Skipped'
end
 

 
--  end try
 -- begin catch
  --end catch
  
  
   SET @Counter  = @Counter  + 1        
END

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453897

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'

Upvotes: 151

Ashraf Sada
Ashraf Sada

Reputation: 4905

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

Upvotes: 4

Filip Holub
Filip Holub

Reputation: 71

You can use Cursor like this:

DECLARE @DBName VARCHAR (64)
DECLARE @SQL VARCHAR (255)

DECLARE DROPDB CURSOR FOR 
SELECT name FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution','ReportServer','ReportServerTempDB')

OPEN DROPDB
FETCH next FROM DROPDB INTO @DBName
WHILE @@FETCH_STATUS = 0

BEGIN
    SET @SQL = 'DROP DATABASE ' + @DBName
    PRINT @SQL
    EXEC @SQL
    FETCH next FROM DROPDB INTO @DBName
END

CLOSE DROPDB
DEALLOCATE DROPDB

In my blog is more about this topic. www.pigeonsql.com

Upvotes: 1

Christopher Bantick
Christopher Bantick

Reputation: 51

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

Upvotes: 5

Gangai Johann
Gangai Johann

Reputation: 888

I've been confronted to a problem with my databases, and the solutions above weren't working.

I simply wanted to delete all my databases, but I had a problem with their names. Indeed, I had databases named like this :

093e83d-somename;
39s2ak3-anothername;

The easier way to delete these database (tested on MariaDB) is to execute the following command :

DROP DATABASE `093e83d-somename`;

This kind of name seems to be a problem when we directly want to execute a SQL command from a bash file, because we have to specify the database's name between back quotes (``).

If you have the same problem, and if you have a lot of databases, you just have to create a batch script with all the commands you need, and then execute this one in your SQL server.

Example with test.sh :

#!/bin/bash

# Informations needed
MUSER="root"
MPASS="pass"

# We get the needed binaries
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)

# We get all the DB names in DB
DB=$($MYSQL -u $MUSER -p$MPASS -e 'show databases' | $AWK '{ print $1}' | $GREP -v '^Databases' )

# For each database, we write the drop command in the file test.sql
for t in $DB
do
    echo -e "DROP DATABASE \`$t\`;" >> test.sql
done

# We execute the created SQL file with all the DROP commands
$MYSQL -u $MUSER -p$MPASS -e 'source test.sql;'

# We finally delete the created file.
rm test.sql

I think that this script is working in all cases. Hope this helped.

Upvotes: 1

sonyisda1
sonyisda1

Reputation: 434

While MartinSmith's answer is the correct solution. I found the Delete Objects window just sitting with status of 'in process' while deleting the first of many databases. This was due to an overgrown msdb backup history which was attempting to be cleaned up with the 'Delete backup and restore history information for databases' option checked.

Refer to these links for more info on cleaning up backup history 1 2

After adding the indexes provided here, the deletion processed through in reasonable time.

Upvotes: 0

Bobby Cannon
Bobby Cannon

Reputation: 6933

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.

Upvotes: 3

Peter Hecht
Peter Hecht

Reputation: 119

I used the answer provided by Pellared and modified it slightly. 

-- 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] like 'DBName%';

-- PRINT @COMMAND
EXECUTE sp_executesql @command

Upvotes: 0

Pellared
Pellared

Reputation: 1292

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

Upvotes: 19

Related Questions