Script that "overwirtes" an object?

If I specify "ScriptDrops" I only get the drops. If I leave this option out then I only get "create" in the script. If I want to overwrite (drop then create new) I am not sure what option(s) to specify. Any ideas?

Thank you.

Kevin

Kevin,

You need to set option IncludeIfNotExists:

so.IncludeIfNotExists = true;

This will generate script with "IF NOT EXISTS ... DROP" -- in other words, check object for existence, then drop it before recreating it.

|||

Artur laksberg MSFT wrote:

Kevin,

You need to set option IncludeIfNotExists:

so.IncludeIfNotExists = true;

This will generate script with "IF NOT EXISTS ... DROP" -- in other words, check object for existence, then drop it before recreating it.

If I include 'IncludeIfNotExists" and drop then is all I get is a conditional drop and the create is ignored. In order for me to overwrite an object I need to drop it and recreate it. Including 'IncludeIfNotExits" and "ScriptDrops" only conditionally produces a drop statement for me. Is that not what you see?

Thank you.

Kevin Burton

|||

Kevin,

We don't have a scripting option for "recreate" but you can achieve the same result by combining Drop and Create script. Here is a simple example I put together:

Code Snippet

static StringCollection RecreateObject( IScriptable scriptableObject )
{
// Generate Drop script
ScriptingOptions so = new ScriptingOptions();
so.ScriptDrops = true;
so.IncludeIfNotExists = true;

StringCollection sc = scriptableObject.Script(so);

// Add batch separator
sc.Add("GO");

// Now generate Crate script and add it to the resulting ScriptCollection
so.ScriptDrops = false;
so.IncludeIfNotExists = false;

foreach( string stmt in scriptableObject.Script(so) )
{
sc.Add(stmt);
}

return sc;
}

public static void Main()
{
Server srv = new Server("your_server_name");
Database db = srv.Databases["pubs"];

foreach( string stmt in RecreateObject(db))
{
Console.WriteLine(stmt);
}

}

For database pubs, function RecreateObject will generate script that looks like this:

Code Snippet

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'pubs')
DROP DATABASE [pubs]
GO
CREATE DATABASE [pubs] ON PRIMARY

...

Note that if you are processing multiple objects, you don't need to drop nested objects. For instance, when processing database and its tables, you don't need to drop tables, since drop of database makes it unnecessary.

Hope that helps.