none
Can't get EnumScript() to generate constraints

    Question

  • I'm trying to get programmatically what I can get manually from SSMS using Tasks > Generate Scripts

    The code below works fine, EXCEPT it doesn't generate any constraints. I don't get any ALTER TABLE [foo] ADD CONSTRAINT ... ON DELETE CASCADE etc etc. I've tried a lot of combinations of Drioptions and on different databases as well. I'm stumped.

    Thanks for insight!

            Scripter scrp = new Scripter(srv)
            {
                Options =
                {
                    ScriptDrops = false,
                    WithDependencies = false,
                    Indexes = true,
                    Triggers = false,
                    Default = true,
                    DriAll = true,
                    //ScriptData = true,
                    ScriptSchema = true,
                }
            };
    
            var urns = new List<Urn>();
    
            foreach (Table tb in db.Tables)
            {
                if (tb.IsSystemObject == false)
                {
                    urns.Add(tb.Urn);
                }
            }
    
            var inserts = scrp.EnumScript(urns.ToArray());
            File.WriteAllLines(path, inserts);
    Wednesday, September 25, 2013 1:07 AM

All replies

  • Hi EricNelson,

    Please modify WithDependencies = true and check test the codes again. There are some similar topics on the following links, you can also refer to them:

    How to automate script generation using SMO in SQL Server?
    http://stackoverflow.com/questions/3488666/how-to-automate-script-generation-using-smo-in-sql-server

    Generate Scripts for database objects with SMO for SQL Server
    http://www.mssqltips.com/sqlservertip/1833/generate-scripts-for-database-objects-with-smo-for-sql-server/

    If you have any feedback on our support, please click here.


    Allen Li
    TechNet Community Support

    Thursday, September 26, 2013 12:59 PM
    Moderator
  • Hi Allen,

    Thanks for your suggestion. That wasn't the solution, though.

    I'm not certain why the code above doesn't work, but here are my suspicions:

    • EnumScript() seems to ignore the Dri options.
    • Even Script() fails to produce the Dri options if I give it an array of Urns for a mix of kinds of objects (e.g. tables and views).

    Those suspicions are just that so take them for what they're worth!

    The solution I found was to use the Script and EnumScript methods of each object I wanted to script; as below. The Script() method is called with {ScriptData=false, ScriptSchema=true} and EnumScript is given {ScriptData=true, ScriptSchema=false}.

                foreach (Table tb in db.Tables)
                {
                    if (tb.IsSystemObject == false)
                    {
                        foreach (var s in tb.Script(schemaOptions))
                            strings.Add(s);
    
                        if (scriptData)
                        {
                            foreach (var i in tb.EnumScript(insertOptions))
                                strings.Add(i);
                        }
                    }
                }


    • Edited by EricNelson Saturday, September 28, 2013 6:36 PM
    Saturday, September 28, 2013 6:35 PM