public CompareSQL CompareTables( TableCollection sourceTables, TableCollection targetTables ) { var tableSQL = new StringBuilder(); var columnSQL = new StringBuilder(); var dropSQL = new StringBuilder(); foreach (Table sourceTable in sourceTables) { if (targetTables.Contains(sourceTable.Name, sourceTable.Schema)) { Table targetTable = targetTables[sourceTable.Name, sourceTable.Schema]; columnSQL.AppendLine(CompareTable(sourceTable, targetTable)); } else { tableSQL.AppendLine(ScriptObjects.ScriptTable(sourceTable)); } } foreach (Table targetTable in targetTables) { if (!sourceTables.Contains(targetTable.Name, targetTable.Schema)) { dropSQL.AppendLine("DROP TABLE IF EXISTS " + targetTable.Schema + "." + targetTable.Name + ";"); } } tableSQL.Append(columnSQL.ToString()); return(new CompareSQL(dropSQL.ToString(), tableSQL.ToString())); }
public static List <DatabaseObject> GetDefaults( Database database ) { var returnList = new List <DatabaseObject>(); foreach (Table table in database.Tables) { if (!table.IsSystemObject) { foreach (Column column in table.Columns) { if (column.DefaultConstraint != null) { string defaultScript = ScriptObjects.ScriptDefault(column.DefaultConstraint, table.Schema, table.Name, column.Name); returnList.Add(new DatabaseObject(DatabaseObjectType.Default, table.Schema, column.DefaultConstraint.Name, defaultScript)); } } } } return(returnList); }
private string CompareTable( Table sourceTable, Table targetTable ) { StringBuilder returnSQL = new StringBuilder(); foreach (Column sourceColumn in sourceTable.Columns) { if (targetTable.Columns.Contains(sourceColumn.Name)) { Column targetColumn = targetTable.Columns[sourceColumn.Name]; if (!IsColumnEqual(sourceColumn, targetColumn)) { returnSQL.AppendLine(ScriptObjects.ScriptColumn(sourceColumn, false)); } } else { returnSQL.AppendLine(ScriptObjects.ScriptColumn(sourceColumn, true)); } } return(returnSQL.ToString()); }
public static List <DatabaseObject> GetUserDefinedTableTypes( Database database ) { var returnList = new List <DatabaseObject>(); foreach (UserDefinedTableType item in database.UserDefinedTableTypes) { returnList.Add(new DatabaseObject(DatabaseObjectType.UserDefinedTableType, item.Schema, item.Name, ScriptObjects.ScriptUserDefinedTableType(item))); } return(returnList); }
public static List <DatabaseObject> GetChecks( Database database ) { var returnList = new List <DatabaseObject>(); foreach (Table table in database.Tables) { if (!table.IsSystemObject) { foreach (Check item in table.Checks) { string schemaName = ((Table)item.Parent).Schema; returnList.Add(new DatabaseObject(DatabaseObjectType.Check, schemaName, item.Name, ScriptObjects.ScriptCheck(item))); } } } return(returnList); }
public static List <DatabaseObject> GetPrimaryKeys( Database database ) { var returnList = new List <DatabaseObject>(); foreach (Table table in database.Tables) { if (!table.IsSystemObject) { foreach (Index item in table.Indexes) { if (item.IndexKeyType == IndexKeyType.DriPrimaryKey) { string schemaName = ((Table)item.Parent).Schema; string tableName = ((Table)item.Parent).Name; returnList.Add(new DatabaseObject(DatabaseObjectType.PrimaryKey, schemaName, item.Name, ScriptObjects.ScriptIndex(item), tableName)); } } } } return(returnList); }
public static List <DatabaseObject> GetForeignKeys( Database database ) { var returnList = new List <DatabaseObject>(); foreach (Table table in database.Tables) { if (!table.IsSystemObject) { foreach (ForeignKey item in table.ForeignKeys) { string schemaName = ((Table)item.Parent).Schema; string tableName = ((Table)item.Parent).Name; returnList.Add(new DatabaseObject(DatabaseObjectType.ForeignKey, schemaName, item.Name, ScriptObjects.ScriptForeignKey(item), tableName)); } } } return(returnList); }