script to drop all 'user' objects

Can someone point me to some sql script that drops all objects for a given
user ?
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.comMichael,
Why do you want to drop all of the objects? Maybe you'd prefer to just
change the ownership of the object? sp_changeobjectowner.
HTH
Jerry
"Michael Tissington" <mtissington@.newsgroups.nospam> wrote in message
news:exFyHU1yFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Can someone point me to some sql script that drops all objects for a given
> user ?
> --
> Michael Tissington
> http://www.oaklodge.com
> http://www.tabtag.com
>
>|||Jerry,
Thanks but I need to drop all the objects for a given users. Too complicated
to explain the details but partially to do with running DTS packages and
limitations in TRUNCATE
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u37AVc1yFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Michael,
> Why do you want to drop all of the objects? Maybe you'd prefer to just
> change the ownership of the object? sp_changeobjectowner.
> HTH
> Jerry
> "Michael Tissington" <mtissington@.newsgroups.nospam> wrote in message
> news:exFyHU1yFHA.3720@.TK2MSFTNGP14.phx.gbl...
>|||Hi Michael,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to drop all objects
that related to one specified user. If I have misunderstood your concern,
please feel free to point it out.
Based on my knowledge, I am afriad we cannot do this directly. However here
are some thoughts
1. get UID from sysusers
2. get object names from sysobjects with this UID
3. according the the name column of Step 2, and delete them separately.
Admittedly, there is a lot of jobs to do. Also, let's wait to see whether
others have the same experience.
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

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.