MS SQL - Delete user created databases

In MS SQL Server, there are different to delete user-generated databases. The simplest way that I found is to use cursor. Note: I explicitly comment out the delete statement to prevent accidental deletions. Remove -- from EXEC('DROP DATABASE ' + @dbname)... for the deletion to work.

DECLARE @cursor_position CURSOR
DECLARE @dbname NVARCHAR(100) -- Need it to hold value of name.
 
-- Set the cursor position from a SQL query.
SET @cursor_position = CURSOR FOR
    SELECT sys.databases.name FROM sys.databases
        WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');
 
-- Fetch the 1st cursor position.
OPEN @cursor_position
FETCH NEXT
    FROM @cursor_position INTO @dbname
 
-- Loop through the cursor.
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @dbname
    -- EXEC('DROP DATABASE ' + @dbname);
 
    -- Fetch the next cursor position.
    FETCH NEXT
        FROM @cursor_position INTO @dbname
END
 
-- Clean up.
CLOSE @cursor_position
DEALLOCATE @cursor_position

MS SQL Server

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.