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

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 2

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 3

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 4

How about:

DECLARE @qry nvarchar(max);

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

EXEC sp_executesql @qry;

Rob