Adding the IF EXISTS...DROP statement

[zberteoc]

How can I change the code to add to the scipt file the IF EXISTS...DROP object dtatement at the top? I checked the code and I noticed that the Scripter class is used but I couldn't find a property that would allow to do that.

[Richard Sutherland]

Try scrp.Options.ScriptDrops = true;

see:
   http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scriptingoptions.scriptdrops.aspx

Hope this helps.

Richard

P.S. When modifying stored procedures, views, etc., you might also consider changing the CREATE predicate to ALTER, in lieu of DROP/CREATE. SQL Server 2005 tracks the LastModified date of objects, but if you drop/re-create them, this field is of no value. In other words, for object types which allow the ALTER predicate, the CREATE predicate would be used but once. All subsequent changes would alter the same filename, but use the ALTER predicate.

[zberteoc]

Thanks,

The problem, though, is that this boolean property only toggles between dropping (True) and creating (False) script and doesn't allow to generate both at the same time. This behaviour is the same as in the scripting wizard in the Management Studio for MS-SQL 2005. Basically if you want to check the existance and drop the object before creating it, which is a commonly used feature, you have to do it in sepparate scripts.

[zberteoc]

Sorry, I didn' get to read your P.S.

I hear you but are there lots of situations when you HAVE to check the existence and drop the object before creating it. ALTER is good but what do you alter if the objects doesn't exist yet and you don't know that? You'll get error either way, trying to create an existing object or trying to alter a non existing one.

[Richard Sutherland]

zberteoc,

Sorry about the inability to script both the DROP/CREATE.

Please help us understand why you want to do that. If that option were available, I would not have used it for this project, as I consider it too dangerous for source management purposes. If one were to accidentally include a DROP/CREATE table in a deployment-to-production, well...  Of course, deployments should be tested on a test server before going to production, eh?

As for stored procedures, views, etc. which have the ALTER predicate, this should be used following the original object creation. The CREATE TABLE, likewise, would be used but once, and ALTER TABLE statements would be used subsequently.

The project's purpose is to produce a starting point for source management. Hopefully, the project's documents "Files, source management, project deployment" [http://sourceforge.net/docman/display_doc.php?docid=44995&group_id=188142] and "Visual SourceSafe Guidelines" [http://sourceforge.net/docman/display_doc.php?docid=46014&group_id=188142] show how it might be used for this purpose.

Please help us understand how you plan on using it.

Thanks.

Richard

[zberteoc]

Rirchard,

I have not suggested that you should change this project for my needs. I only wanted to know how I could do that if I needed. That's it. However, if there was an option that said "generate the drop statements as well" would have been nice.

But because you asked me so nicely, :o), I will tell you what we do. We use an applications that is based on code that resides in tables in a database. You can develop the app by adding new screen and with new functionality all these being added to some special tables used by the app. In this process very often we build new or alter database objects like tables, procs, views and functions. Whatever passes the tests goe's to PROD. From time to time we make a synch betweed our development environtment (database) and the production one because the dev always will have the last code (currently under development) while production would have the up to date data in its tables. We do this by saving the data from code tables from dev, generating the scripts for views, store procs and functions, restoring from last prod backup and then applying again to DEV what we saved in the first two steps because we want to preserve our lates code  and changes and objects from before synch. When we save the procs, views and fns we don't want to waste time by picking which one to alter and which one to create but we want to generate the scripts for all of them by dropping and creating again. You have to agree with me that is simpler this way. Also we would like to do this in an automated way and not byusing wizzards (as we do right now). Here is where your Data Dude could come to help, except for this little detail with the drop statements. :o)

Hope I was clear enough.

[Richard Sutherland]

Here is a snippet of code with a sample of how you could modify the code to
include the DROP/CREATE. This sample for tables would need to be put in the
corresponding section of code for each object type for which a DROP/CREATE
was desired.

   filename = argPath + tbl.Schema.Replace(@"\", @"$") + "." + tbl.Name + ".table.sql";
   Console.WriteLine("  Table: " + tbl.Schema + "." + tbl.Name);
  
   scrp.Options.ScriptDrops = true;
   scrp.Options.IncludeIfNotExists = true;
  
   // script the drop table
   ScriptIt(urn, scrp, filename);
  
   scrp.Options.ScriptDrops = false;
   scrp.Options.IncludeIfNotExists = false;
   scrp.Options.AppendToFile = true;
  
   // script the create table
   ScriptIt(urn, scrp, filename);
  
   scrp.Options.AppendToFile = false;

[Richard Sutherland]

zberteoc,

Actually this might be a good discussion, but I'd like to take it off-line for a while, then post the results as a summary.

I'll email you.

Richard