Drop a table if it exists then recreate it

ttist25
ttist25 used Ask the Experts™
on
I'm trying to do a SQL statement (SQL Server 2012) that looks to see if a database exists and, if it does, drop the database and then recreate the database.

Here's what I have so far:
     IF EXISTS(SELECT * FROM sys.databases WHERE NAME='[MyDatabase]')
     BEGIN
       ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
     DROP DATABASE [MyDatabase];
       END
       ELSE
     BEGIN
       CREATE DATABASE [MyDatabase]
       END

When I run this I get the error:
Database 'MyDatabase' already exists.  Choose a different database name.

Whut?

TIA!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
The brackets are not in the name:

 IF EXISTS(SELECT * FROM sys.databases WHERE NAME='MyDatabase') --<<-- note brackets removed from name

Btw, much,much better and safer to set the db offline rather than to single_user.  If a user happens to be in the db at the time, you may not be able to drop it.

ALTER DATABASE [MyDatabase] SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [MyDatabase] SET ONLINE;
DROP DATABASE [MyDatabase];

Author

Commented:
Man Scott.  It seems like you're saving my butt daily!!!!

I'm using pyodbc and a python script to do this work and reading in the table name as a variable there.  I surrounded the variable with brackets thinking it would save me time.  

HAH!  

Thanks again!!!!!

PS thanks for the "offline" tip
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
You're welcome!!  Glad it helped.

You can get into SINGLE_USER hell sometimes, where you can't access the db for several minutes.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial