Revisions to Drop all databases based on age

formatted code and output

Source Link

edited Sep 27, 2019 at 5:20

18.3k
14
54
115

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

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

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

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

CLOSE DROPDB

Script

DEALLOCATE DROPDB

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

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

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

added 6 characters in body

Source Link

edited Sep 27, 2019 at 5:11

vicky

59
1
7

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

DECLARE @SQL VARCHAR (255)

DECLARE DROPDB CURSOR FOR SELECT

SELECT name FROM sys.databases WHERE create_date <= '20190726' AND name NOT IN ('master','model','msdb','tempdb','distribution','ReportServer','ReportServerTempDB')

OPEN DROPDB FETCH

FETCH next FROM DROPDB INTO @DBName WHILE

WHILE @@FETCH_STATUS = 0

BEGIN SET @SQL = 'DROP DATABASE ' + @DBName PRINT

PRINT @SQL EXEC

EXEC @SQL FETCH

FETCH next FROM DROPDB INTO @DBName END

END

CLOSE DROPDB DEALLOCATE

DEALLOCATE DROPDB

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

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

added 668 characters in body

Source Link

edited Sep 27, 2019 at 4:53

vicky

59
1
7

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 win the listwith 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

Source Link

asked Sep 27, 2019 at 4:45

vicky

59
1
7

Loading

Stack Exchange Network

Return to Question

Error

Script

Error

Script