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

  • Resender

    SSCertifiable

    Points: 7285

    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

    SSCertifiable

    Points: 7285

    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

    Ten Centuries

    Points: 1016

    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);

    }

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply