SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can I use SMO to script out schema objects from a database ?


Can I use SMO to script out schema objects from a database ?

Author
Message
Resender
Resender
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6449 Visits: 1640
Hello all,we have a couple of databases that have constant data change capture on them,without going in much detail it is allmost impossible storage wise to keep a consisten databackup on these & we don't really need one as the source system already has those if needed.

However we need to maintain the structure so each week we want to run something that will generate an sql script for all the objects in the database.

I have a script that does that,tested out very nicely,only got 2 issues left to resolve,getting the users & getting the schema.
For instance we can have tables abc.def or def.abc
I need to have the create script for abc. & def.

I followed most of the stuff I could find about smo but when I try the schema's I get the following error

Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Discover dependencies failed. ---> Microsoft.SqlServer.Management.Smo.InternalEnumeratorException: Schema is not supported in dependency discovery. Only objects of the following types are supported: UserDefinedFunction, View, Table, StoredProcedure, Default, Rule, Trigger, UserDefinedAggregate, Synonym, UserDefinedDataType, XmlSchemaCollection, UserDefinedType, PartitionScheme, PartitionFunction, SqlAssembly.


Is their a way around this issue.
PS
I'm making this in C# a language I had 0 experience with yesterday.
Resender
Resender
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6449 Visits: 1640
OK I kind of solved by doing this

var schemaList = new List<string>();

string dbUse = "USE [" + dbName + "]";

foreach (Schema sch in db.Schemas)
{
string schName = sch.Name.ToString();
if (schName != "db_accessadmin")
{
if (schName != "db_backupoperator")
{
if (schName != "db_datareader")
{
if (schName != "db_datawriter")
{
if (schName != "db_ddladmin")
{
if (schName != "db_denydatareader")
{
if (schName != "db_denydatawriter")
{
if (schName != "db_owner")
{
if (schName != "db_securityadmin")
{
if (schName != "guest")
{
if (schName != "INFORMATION_SCHEMA")
{
if (schName != "sys")
{
if (schName != "dbo")
{
string schCreate = "CREATE SCHEMA " + sch.ToString() + " AUTHORIZATION [" + sch.Owner.ToString() + "]";
schemaList.Add(dbUse);
schemaList.Add("GO");
schemaList.Add(schCreate);
schemaList.Add("GO");
//string schn = sch.ToString();
//string schO = sch.Owner.ToString();
}
}
}
}
}
}
}
}
}
}
}
}
}
}



Now the next challenge is to get the database create script including datafiles
Joshua D Gang
Joshua D Gang
SSC Eights!
SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)

Group: General Forum Members
Points: 884 Visits: 308
Wow that is alot of work

Why arent you using the SMO option. I have other options but I would suggest that you research what you need.

ScriptingOptions scropt = new ScriptingOptions();

scropt.ScriptSchema = true;
scropt.AllowSystemObjects = false;

SchemaCollection schemacoll;
schemacoll = db.Schemas;

foreach (Schema schemas in schemacoll)
{
schemas.Script(scropt);
}
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search