Can't get EnumScript() to generate constraints


  • 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)
            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?

    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
  • 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))
                        if (scriptData)
                            foreach (var i in tb.EnumScript(insertOptions))

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