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);
}