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