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