Skip to main content
2 of 4
added 668 characters in body
vicky
  • 59
  • 1
  • 7

Drop all databases based on age

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')

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'.

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

vicky
  • 59
  • 1
  • 7