Drop databases with no tables

Question

Is there a query which drops / deletes databases with no tables in them (deletes empty databases)?

Server is Microsoft SQL Server 2005

Answer 1

This should do it. Tested on a lab machine and it dropped all databases with 0 user tables. Note, however, that tables aren't the only things in a database, necessarily. There could be stored procedures, functions, etc that someone might still need.

NOTE THAT THIS IS A VERY DANGEROUS OPERATION, AS IT DROPS DATABASES. USE AT YOUR OWN RISK. I AM NOT RESPONSIBLE FOR DAMAGE YOU CAUSE.

USE [master];
DECLARE @name varchar(50);
DECLARE @innerQuery varchar(max);
DECLARE tableCursor CURSOR FOR SELECT name FROM sys.databases where owner_sid != 0x01;
OPEN tableCursor;

FETCH NEXT FROM tableCursor
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @innerQuery = 'USE [' + @name + ']; IF (SELECT COUNT(*) FROM sys.objects WHERE type = ''U'') = 0
    BEGIN
        USE [master];
        DROP DATABASE [' + @name + ']
    END'
    EXEC(@innerQuery)
    FETCH NEXT FROM tableCursor INTO @name
END

CLOSE tableCursor;
DEALLOCATE tableCursor;

Note also that, if a database is in use, SQL Server will refuse to drop it. So, if there are other connections to a particular database that this tries to drop, the command will abort.

To avoid that problem, you can set the database in question to single-user mode.

The following script is the same as the above, except it also sets the target databases to single-user mode to kill active connections. BE EVEN MORE CAREFUL WITH THIS, AS IT'S ESSENTIALLY THE NUCLEAR OPTION:

use [master];
DECLARE @name varchar(50);
DECLARE @innerQuery varchar(max);
DECLARE tableCursor CURSOR FOR SELECT name FROM sys.databases where owner_sid != 0x01;
OPEN tableCursor;

FETCH NEXT FROM tableCursor
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @innerQuery = 
    'USE [' + @name + '];
    IF (SELECT COUNT(*) FROM sys.objects WHERE type = ''U'') = 0
    BEGIN
        USE [master];
        ALTER DATABASE [' + @name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
        DROP DATABASE [' + @name + '];
    END'
    EXEC(@innerQuery)
    FETCH NEXT FROM tableCursor INTO @name
END

CLOSE tableCursor;
DEALLOCATE tableCursor;

Answer 2

I think it is not possible (at least not with a TSQL command, might be a stored procedure somewhere). You would have to query sysobjects and abort if any found. (And you have to decide if you want to ignore some of the system objects like the design tables).

Answer 3

The script below will generate the needed DROP DATABASE script. You could tweak this to execute the statement too (in the master database context) but I suggest you review it first just in case.

EXEC sp_MSforeachdb N'
    USE [?];
    IF N''?'' NOT IN(N''master'', N''model'', N''msdb'', N''tempdb'')
    BEGIN
        IF NOT EXISTS(
            SELECT *
            FROM sys.tables
            WHERE
                OBJECTPROPERTYEX(object_id, ''IsMSShipped'') = 0
            )
        BEGIN
            PRINT ''DROP DATABASE [?];'';
        END;
    END;';