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!
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Scott Pletcher

You're welcome!!  Glad it helped.

You can get into SINGLE_USER hell sometimes, where you can't access the db for several minutes.
Your help has saved me hundreds of hours of internet surfing.
fblack61