Drop all databases based on age

Question

How can we drop all databases based on age.

If i go with today's date with 2 months retention the list will come with below query , now i want to drop all the databases with query output.

SELECT name as [database] , database_id, create_date  
FROM sys.databases where create_date  <= '20190726' AND name NOT IN ('Master','Model','MSDB','tempdb')

Error

The below script not working with errors being:

DROP DATABASE VM_P
Msg 2812, Level 16, State 62, Line 15
Could not find stored procedure 'DROP DATABASE VM_P.

Script

DECLARE @DBName VARCHAR (64)
DECLARE @SQL VARCHAR (255)
DECLARE DROPDB CURSOR FOR 
SELECT name FROM sys.databases 
WHERE create_date  <= '20190726' 
AND 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

Answer 1

Your code is very, very close to working.

It's not explicitly clear from the docs but the examples do show this.

When using EXEC (or EXECUTE) with a stored procedure, you write the object name straight after the statement (example 1 below), however when calling a T-SQL string you must include brackets (example 2 below)

Example 1

EXEC dbo.Procedure

Example 2

EXEC ('SELECT * FROM dbo.SomeTable')

As such, your solution should look like this (Note the replacement of the static date with GETDATE() to satisfy your original condition of dropping based on the age of the database):

--For internal objects, always use NVARCHAR - this is what the engine uses internally in most places
DECLARE @DBName NVARCHAR (64)
DECLARE @SQL NVARCHAR (255)
DECLARE DROPDB CURSOR FOR 
SELECT name FROM sys.databases 
--Use GETDATE() with DATEADD for -60 days to get the threshold for deletion
WHERE create_date  <= DATEADD(DD,-60,GETDATE())
AND 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 ' + QUOTENAME(@DBName)
            PRINT @SQL
    EXEC (@SQL)
    FETCH next FROM DROPDB INTO @DBName
END
CLOSE DROPDB
DEALLOCATE DROPDB

As an aside, given that this is such a potentially damaging script, I'd drop the EXEC and simply copy the printed statements into a new query to run them. This would avoid any unforeseen accidents as it gives you an opportunity to audit the script before you run it.

Answer 2

You should make a small changes in your script

Replace EXEC @SQL with EXEC sp_executesql @SQL or EXEC (@SQL) This works fine

DECLARE @DBName VARCHAR (64)
DECLARE @SQL VARCHAR (256) 
DECLARE DROPDB CURSOR FOR 
SELECT name FROM sys.databases 
WHERE create_date  <= '20190726' 
AND 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 sp_executesql @SQL --OR EXEC (@SQL)
    FETCH next FROM DROPDB INTO @DBName
END
CLOSE DROPDB
DEALLOCATE DROPDB