/// <summary> /// Creates a database table for each DataTable that's part of a BacksightDataSet, /// inserts initial rows, and defines constraints. /// </summary> /// <param name="logger">Something to display progress messages (not null)</param> /* * public void CreateTables(ILog logger) * { * try * { * if (logger == null) * throw new ArgumentNullException(); * * //Transaction.Execute(delegate * //{ * CreateBacksightTables(logger); * //}); * } * * catch (Exception ex) * { * logger.LogMessage(ex.Message); * throw ex; * } * } */ /* * void CreateBacksightTables(ILog logger) * { * DropForeignKeyConstraints(); * * // Create the ced schema * logger.LogMessage("CREATE SCHEMA ced"); * Smo.Schema s = new Smo.Schema(m_Database, "ced"); * s.Create(); * * BacksightDataSet ds = new BacksightDataSet(); * foreach (DataTable dt in ds.Tables) * { * logger.LogMessage("CREATE TABLE " + GetTableName(dt)); * CreateTable(s, dt); * } * * // Add simple checks (unfortunately, this info isn't held as part of the * // generated DataSet, so need to explicitly identify each table involved) * * AddSimpleChecks(logger, ds.EntityType, ds.EntityType.Checks); * AddSimpleChecks(logger, ds.EntityTypeSchema, ds.EntityTypeSchema.Checks); * AddSimpleChecks(logger, ds.Font, ds.Font.Checks); * AddSimpleChecks(logger, ds.IdAllocation, ds.IdAllocation.Checks); * AddSimpleChecks(logger, ds.IdFree, ds.IdFree.Checks); * AddSimpleChecks(logger, ds.IdGroup, ds.IdGroup.Checks); * AddSimpleChecks(logger, ds.Layer, ds.Layer.Checks); * AddSimpleChecks(logger, ds.Schema, ds.Schema.Checks); * AddSimpleChecks(logger, ds.SchemaTemplate, ds.SchemaTemplate.Checks); * AddSimpleChecks(logger, ds.Template, ds.Template.Checks); * AddSimpleChecks(logger, ds.Theme, ds.Theme.Checks); * * // Insert initial rows & save to database * logger.LogMessage("Inserting initial rows"); * ds.AddInitialRows(); * ds.Save(ConnectionString); * * // Define foreign key constraints * AddForeignKeyConstraints(logger); * } */ /* * void AddSimpleChecks(ILog logger, DataTable dt, string[] checks) * { * string tableName = GetTableName(dt); * Smo.Table t = m_Database.Tables[tableName, "ced"]; * if (t==null) * throw new Exception("Cannot locate table ced."+tableName); * * for (int i=0; i<checks.Length; i++) * { * string checkName = String.Format("{0}Check{1}", t.Name, i+1); * logger.LogMessage("ADD CHECK "+checkName); * AddCheck(t, checkName, checks[i]); * } * } */ /* * void AddForeignKeyConstraints(ILog logger) * { * BacksightDataSet ds = new BacksightDataSet(); * * foreach (DataRelation dr in ds.Relations) * { * DataTable parent = dr.ParentTable; * DataTable child = dr.ChildTable; * * string parentTableName = GetTableName(parent); * string childTableName = GetTableName(child); * * Smo.Table parentTable = m_Database.Tables[parentTableName, "ced"]; * Smo.Table childTable = m_Database.Tables[childTableName, "ced"]; * * if (parentTable!=null && childTable!=null) * { * if (logger!=null) * logger.LogMessage("ADD CONSTRAINT "+dr.RelationName); * * AddForeignKeyConstraint(dr); * } * } * } */ /* * void DropForeignKeyConstraints() * { * BacksightDataSet ds = new BacksightDataSet(); * List<Smo.ForeignKey> fks = new List<Smo.ForeignKey>(); * * foreach (DataTable dt in ds.Tables) * { * string tableName = GetTableName(dt); * Smo.Table t = m_Database.Tables[tableName, "ced"]; * if (t!=null) * { * foreach (Smo.ForeignKey fk in t.ForeignKeys) * fks.Add(fk); * } * } * * foreach (Smo.ForeignKey fk in fks) * fk.Drop(); * } */ void CreateTable(Smo.Schema s, DataTable dt) { // Drop any previously created version of the table string tableName = GetTableName(dt); Smo.Table t = m_Database.Tables[tableName, "ced"]; if (t != null) { t.Drop(); } // Create the table t = new Smo.Table(m_Database, tableName); t.Schema = s.Name; foreach (DataColumn c in dt.Columns) { AddColumn(t, c); } t.Create(); // Define primary key CreatePrimaryKey(t, dt); // Define pk & any unique constraints CreateIndexes(t, dt); }
public MySmo.Schema GetSchema(Smo.Schema smo_s, MySmo.Database parent = null) { #region implement SetDataLimit(); var mysmo_s = new MySmo.Schema(); mysmo_s.ParentDatabase = parent; mysmo_s.Name = smo_s.Name; mysmo_s.Owner = smo_s.Owner; mysmo_s.ExtendedProperties = GetExtendProperties(mysmo_s, smo_s.ExtendedProperties); FormatExtendProperties(mysmo_s); return(mysmo_s); #endregion }
public void ExtractScript(SQLObjectName oname, SQLScripts SQLScriptsCollection, bool Verbose) { // Store extracted scripts. Each extract may include multiple scripts. StringCollection OutputScripts = new StringCollection(); string FinalScript = String.Empty; switch (oname.ObjectType) { case SQLObjectType.Table: Microsoft.SqlServer.Management.Smo.Table scriptTable = connDatabase.Tables[oname.Name, oname.Schema]; if (scriptTable != null) { StringCollection CheckScripts = new StringCollection(); // Store scripts to be checked String TableScript = String.Empty; // Stores individual script for output collection. ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.DriAll = true; scriptOptions.Statistics = true; scriptOptions.ClusteredIndexes = true; scriptOptions.NonClusteredIndexes = true; scriptOptions.DriAllConstraints = true; scriptOptions.WithDependencies = false; // Get table and related scripts CheckScripts = scriptTable.Script(scriptOptions); // Check scripts so we can remove invalide SQL 2012 column store options from the script. // (Why doesn't the target server version remove this? // This is a crappy place to do this, and it's version specific. // Need to implement the new versioning code to check target model. foreach (string CheckCCI in CheckScripts) { if (CheckCCI.Contains(", DATA_COMPRESSION = COLUMNSTORE")) { TableScript = CheckCCI.Replace(", DATA_COMPRESSION = COLUMNSTORE", ""); } else { TableScript = CheckCCI; } // Add the script into the OutputScripts collection. OutputScripts.Add(TableScript); } } break; case SQLObjectType.View: Microsoft.SqlServer.Management.Smo.View scriptView = connDatabase.Views[oname.Name, oname.Schema]; if (scriptView != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.DriAll = true; scriptOptions.ClusteredIndexes = true; scriptOptions.NonClusteredIndexes = true; scriptOptions.WithDependencies = false; // Must specify tables seperatly, but safer to do so // to avoid having duplicate table names in the model. OutputScripts = scriptView.Script(scriptOptions); } break; case SQLObjectType.StoredProcedure: Microsoft.SqlServer.Management.Smo.StoredProcedure scriptStoredProcedure = connDatabase.StoredProcedures[oname.Name, oname.Schema]; if (scriptStoredProcedure != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; OutputScripts = scriptStoredProcedure.Script(scriptOptions); } break; case SQLObjectType.PartitionScheme: { Microsoft.SqlServer.Management.Smo.PartitionScheme scriptPScheme = connDatabase.PartitionSchemes[oname.Name]; if (scriptPScheme != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; OutputScripts = scriptPScheme.Script(scriptOptions); } } break; case SQLObjectType.PartitionFunction: { Microsoft.SqlServer.Management.Smo.PartitionFunction scriptPFunction = connDatabase.PartitionFunctions[oname.Name]; if (scriptPFunction != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; OutputScripts = scriptPFunction.Script(scriptOptions); } } break; case SQLObjectType.Schema: { Microsoft.SqlServer.Management.Smo.Schema scriptSchema = connDatabase.Schemas[oname.Name]; if (scriptSchema != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; scriptOptions.ScriptOwner = true; // This includes the "with authorize" part. OutputScripts = scriptSchema.Script(scriptOptions); } } break; case SQLObjectType.FileGroup: { Microsoft.SqlServer.Management.Smo.FileGroup scriptFG = connDatabase.FileGroups[oname.Name]; if (scriptFG != null) { // Create manual script for FileGroups OutputScripts.Add("ALTER DATABASE [$(DatabaseName)] ADD FILEGROUP " + scriptFG.Name); } } break; case SQLObjectType.User: { Microsoft.SqlServer.Management.Smo.User scriptUser = connDatabase.Users[oname.Name]; if (scriptUser != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; OutputScripts = scriptUser.Script(scriptOptions); } } break; case SQLObjectType.Function: Microsoft.SqlServer.Management.Smo.UserDefinedFunction userDefinedFunction = connDatabase.UserDefinedFunctions[oname.Name, oname.Schema]; if (userDefinedFunction != null) { ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.WithDependencies = false; OutputScripts = userDefinedFunction.Script(scriptOptions); } break; } if (OutputScripts.Count > 0) { Console.WriteLine("Extracted SQL script: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name)); foreach (string script in OutputScripts) { // Add the script to the script collection. FinalScript = FinalScript + script + Environment.NewLine + "GO" + Environment.NewLine; } } else { Console.WriteLine("Warning - Could not retrieve: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name)); FinalScript = String.Empty; } if (FinalScript != String.Empty) { SQLScriptsCollection.Scripts.Add(FinalScript); } else { SQLScriptsCollection.MissingScripts.Add("Missing SQL object: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name)); } // Print script(s) if verbose is on. if (Verbose) { Console.WriteLine(FinalScript); } }