Using SMO to create a script for table defaults

Question

I am trying to create a database scripter tool for the local database that I am using.

I managed to create creation scripts for tables, primary keys, indexes, and foreign keys, but I cannot find a way to generate creation scripts for table defaults.

For indices, it's just like

foreach (Index index in table.Indexes) { ScriptingOptions drop = new ScriptingOptions(); drop.ScriptDrops = true; drop.IncludeIfNotExists = true; foreach (string dropstring in index.Script(drop)) { createScript.Append(dropstring); } ScriptingOptions create = new ScriptingOptions(); create.IncludeIfNotExists = true; foreach (string createstring in index.Script(create)) { createScript.Append(createstring); } } 

But the Table object does not have the Defaults property. Is there any other way to generate scripts for tabular defaults?

Answer 1

Try using a Scripter object with the DriAll option:

 Server server = new Server(@".\SQLEXPRESS"); Database db = server.Databases["AdventureWorks"]; List<Urn> list = new List<Urn>(); DataTable dataTable = db.EnumObjects(DatabaseObjectTypes.Table); foreach (DataRow row in dataTable.Rows) { list.Add(new Urn((string)row["Urn"])); } Scripter scripter = new Scripter(); scripter.Server = server; scripter.Options.IncludeHeaders = true; scripter.Options.SchemaQualify = true; scripter.Options.SchemaQualifyForeignKeysReferences = true; scripter.Options.NoCollation = true; scripter.Options.DriAllConstraints = true; scripter.Options.DriAll = true; scripter.Options.DriAllKeys = true; scripter.Options.DriIndexes = true; scripter.Options.ClusteredIndexes = true; scripter.Options.NonClusteredIndexes = true; scripter.Options.ToFileOnly = true; scripter.Options.FileName = @"C:\tables.sql"; scripter.Script(list.ToArray()); 

Answer 2

Until I used SMO, I was looking for MSDN and this is what I found.

A table has the Columns property (a collection of columns), which should have a link to each column.
Each column will have the DefaultConstraint property.

Is this what you are looking for?

Answer 3

Addition to Paul's answer.

I needed to get the script only for the invidual table. 1. I wanted to pass the schema name of the schema and the name of the table as a parameter and generate a script. 2. Assign the script to a variable, not a file entry.

for a separate table:

 /*get a particular table script only*/ Table myTable = db.Tables["TableName", "SchemaName"]; scripter.Script(new Urn[] { myTable.Urn}); 

Write a script variable:

 StringCollection sc = scripter.Script(new Urn[] { myTable.Urn }); foreach (string script in sc) { sb.AppendLine(); sb.AppendLine("--create table"); sb.Append(script + ";"); } 

I hope this helps future readers.