T-SQL CREATE VIEW returning 'There is already an object named x'

Question

SQL Server 2014. I have a database on which I created a view called 'Sales-Analysis'. I also have a second database with the same schema as the first and I want to add the view to that. So in SSMS I performed 'Script view as CREATE to new Query Editor window'. I changed the first line of the script to reflect the name of the second database and ensured it was selected in the SSMS databases dropdown too. However when I execute I get:

"There is already an object named 'sales-analysis' in the database."

There isn't other than the one in the original database. No temporary tables involved. VS 2015 is closed.

It also doesn't work even if I take the original db offline, or if I save the script as an .SQL file and execute it with SQLCMD.

Same server. Refreshed views. Can successfully create a view with a '2' appended into the target database. It's just when the view name is the same as the original database. use [database] is already at the beginning of the script -'Script view' adds it. I changed it to the target database name. The view 100% does not exist in the target db. I checked via querying sys.views.

Answer 1

Turns out the target db had a TABLE (not view) with the same name. I didn't create it, I suspect what has happened is that an Entity Framework project which I have created it when I changed the app.config connection string to point at the second database and ran the project.

Naughty Entity Framework.

Answer 2

Add a drop statement in front of your create.

Take note that this is dangerous.

• RDFozz: The reason this is a dangerous suggestion is that the OP didn't know what the other object was. Since it turned out to be a table, DROP VIEW wouldn't have worked anyway. While the suggestion is generally a good one, in this case the user would want to confirm that the object being deleted (if it had been a view) was not in use, serving some other purpose.