Drop tables before creating them VS 2012 DB project

Question

I would like to drop the tables included in the database project before creating them so each time I publish the project I do a full build of those tables (as by default it seems to work as an incremental build).

I've seen options to drop the tables (and other entities) that exists in the target DB but not in the source project and what I would like is more or less the opposite. Drop the tables that exists in the DB project from the target before creating them.

FYI this related SO question that just showed up... I thought you might be able to help this guy... or, that you might be interested in the answers, that are going to be posted on his question: stackoverflow.com/questions/20555237/… — Doug_Ivison
– Doug_Ivison, Commented Dec 12, 2013 at 22:18

Doug_Ivison · Accepted Answer · 2013-12-12 14:55:44Z

-1

A simple way to do it, is to put it in SQL code, before the CREATE TABLE:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'zzWorkTable1')
    DROP TABLE dbo.[zzWorkTable1]

Or, if you're doing it in another database:

IF EXISTS (SELECT * FROM [<db_name>].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'zzWorkTable1')
    DROP TABLE [<db_name>].dbo.[zzWorkTable1]

If you want some other approaches... can you tell us a little about the project, like what kind of steps create the tables?

answered Dec 12, 2013 at 14:55

Doug_Ivison

6487 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Wallack Over a year ago

The project is a DB project in VS 2012 that allows you to do a "drag n drop" from the SQL Server explorer of the tables you want to include and then to publish it somewhere instead of copy and pasting the queries you can actually deploy the project. Good thing is that it deploys what is needed but sometimes I may need to do a full deploy and I was expecting an option that I may be missing somewhere. I already have those queries in my scripts but is incompatible if I want the script to behave like a table in VS and not just a random script.

Doug_Ivison Over a year ago

@Wallack Interesting... if you want a script to behave like a table, you may want to make that script into a VIEW. As far as info for "other approaches" go, it would help to know a little more technically -- like, how are tables created -- so I can suggest a DROP TABLE approach to go in the same place as the creation... if that helps you. Also, what kind of project? Is this happening in a console application, in a web application, in SSRS (Reporting Services), or in SSIS (Integration Services -- less likely, since I'd heard that, out of the box, VS 2012 did not handle SSIS development)?

Wallack Over a year ago

You have misunderstood me probably! VS 2012 has the option to create a DB project with the same structure as a DB (tables, views, triggers, indexes ...) and it will try to compile it before deploy to see its integrity and then do the deploy in the specified location. Good thing about this is that you can keep your DB updated as you can have the project in TFS along with the rest of your data. The thing is that each time you deploy this project it only create the tables that doesn't exist or need to be rebuilt.

Doug_Ivison Over a year ago

@Wallack Yes, those details help a lot... (1) note from THIS LINK -- "When you prepare to deploy a Web application project that uses one or more SQL Server databases, you can enter settings that specify database scripts that must run during deployment." So, how about running scripts during deployment like the t-sql code I gave above... but the scripts would have to run first. (2) I only know TFS basics, but wonder if TFS could help... can you substitute TFS for one of this question's tags, to attract TFS suggestions?

Wallack Over a year ago

There is a way to specify whether the scripts run after or before the deployment (for this scenario drops would before, and inserts after) but the thing is that those settings are set directly in the scripts level and not in the profile level. The profile is what you choose to deploy to so ideally I would have two profiles one for incremental (wouldn't do drops) and one for full builds (would drop tables) so I have to keep wondering about this.

Collectives™ on Stack Overflow

Drop tables before creating them VS 2012 DB project

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related