deleting databases in sql server

Question

I need to delete databases whose name start with "In"...

I Tried "Like" but it is throwing syntax errors...

I tried following command but throwing syntax errors for "name"

SELECT 'DROP DATABASE ' + name + ';' from sys.databases where name like 'In%'

Answer 1

How about:

DECLARE @qry nvarchar(max);

SELECT @qry = 
(SELECT 'DROP DATABASE ' + QUOTENAME(name) + '; ' 
FROM sys.databases 
WHERE name LIKE 'In%'
FOR XML PATH(''));

EXEC sp_executesql @qry;

Answer 2

The syntax is

DROP DATABASE { database_name | database_snapshot_name } [ ,...n ] [;]

It does not allow for wildcards or where clauses. You would have to do that manually or programmatically, getting the names of the databases from sys.databases.

Answer 3

you can create a CURSOR that loops over

SELECT name FROM sys.databases WHERE name LIKE 'ln%'

and inside the loop you create the drop statement and execute it

SET @s = 'DROP DATABASE ' + @name
EXEC (@s)

not sure whether that works, though, and I don't have any databases to drop to test this ;)

Answer 4

USE master;

DECLARE @Temp TABLE (Id INT IDENTITY(1, 1), name nvarchar(max));
DECLARE @RowCounter INT;

INSERT INTO @Temp
select name from sys.databases where name like N'In%';

SELECT @RowCounter = MAX(Id) FROM @Temp;

DECLARE @i INT;
SET @i = 1;

DECLARE @DBDeleteByName NVARCHAR(max);

WHILE @i <= @RowCounter
BEGIN
    ----- Build the drop database sql.
    SELECT @DBDeleteByName = 'DROP DATABASE ' + name 
    FROM @Temp WHERE Id = @i;

    ----- Drop the database.
    EXEC sp_executesql @DBDeleteByName;

    ----- Increment counter
    SET @i = @i + 1;

END

Answer 5

You could create a SQL statement to generate a script for you:

SELECT 'DROP DATABASE ' + name + ';' from sys.databases where name like 'In%'

and then run that script.

So if you had two databases InTestDB1 and InTestDB2 the above would generate the following results:

DROP DATABASE InTestDB1;
DROP DATABASE InTestDB2;

You could then save that to a file to run as a script on another server or copy it directly into query analyzer to run immediately.