delete/drop variable/generated SQL database in T-SQL
-
G'day all,
I have a situation here where some onfloor systems are generating a new database each day with a new name based on the date. (Its a stupid thing, I know, but until the programmers do something about it, I'm stuck with it).
The problem is that we end up with a lot of empty databases left behind and I need to go around, once a month, and manually delete/drop all of these.
I tried to write some code for a scheduled task to kill the old databases each day, but the drop database command doesn't seem to like generated database names or database names, held in a variable.
EG1: DROP DATABASE ('TMP' + REPLACE(CONVERT(char(8), GETDATE()-7, 3), '/', ''))
EG2: SELECT @DBVARIABLE = 'TMP' + REPLACE(CONVERT(char(8), GETDATE()-7, 3), '/', '')
DROP DATABASE @DBVARIABLE
I tried these, and a number of different methods to pass a generated database name but they all failed.
QUESTION: How can I delete/drop a database based on a generated database name?
Thanks in advance
Charles Wilkinson, Database Administrator, Sastek Pty Ltd
Kindest Regards,Charles Wilkinson
Database Administrator
Sastek Pty Ltd
www.sastek.com -
You're almost there with your second example. Just wrap the drop in an EXEC statement.
EG: EXEC('DROP DATABASE ' + @DBVARIABLE)Also, you could simplify creating the database name to
SELECT @DBVARIABLE = 'TMP' + CONVERT(char(8), GETDATE()-7, 12)
--------------------
Colt 45 - the original point and click interface
-
ITS ALIVE!!!
(I think I tried the EXEC angle but left out the brackets *shrug*)
Thanks Phill
Kindest Regards,Charles Wilkinson
Database Administrator
Sastek Pty Ltd
www.sastek.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply