Link to home
Start Free TrialLog in
Avatar of ttist25
ttist25

asked on

Drop a table if it exists then recreate it

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!
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ttist25
ttist25

ASKER

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
You're welcome!!  Glad it helped.

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