private static HashSet <SchemaName> DefaultGetSchemas(List <DatabaseName> list)
{
HashSet <SchemaName> result = new HashSet <SchemaName>();
foreach (var db in list)
{
using (Administrator.OverrideDatabaseInSysViews(db))
{
var schemaNames = Database.View <SysSchemas>().Select(s => s.name).ToList().Except(SqlBuilder.SystemSchemas);
result.AddRange(schemaNames.Select(sn => new SchemaName(db, sn)));
}
}
return(result);
}
private static IDisposable DropAndCreateForeignKeys(Table table)
{
var foreignKeys = Administrator.OverrideDatabaseInSysViews(table.Name.Schema.Database).Using(_ =>
(from targetTable in Database.View <SysTables>()
where targetTable.name == table.Name.Name && targetTable.Schema().name == table.Name.Schema.Name
from ifk in targetTable.IncommingForeignKeys()
let parentTable = ifk.ParentTable()
select new
{
Name = ifk.name,
ParentTable = new ObjectName(new SchemaName(table.Name.Schema.Database, parentTable.Schema().name), parentTable.name),
ParentColumn = parentTable.Columns().SingleEx(c => c.column_id == ifk.ForeignKeyColumns().SingleEx().parent_column_id).name,
}).ToList());
foreignKeys.ForEach(fk => SqlBuilder.AlterTableDropConstraint(fk.ParentTable !, fk.Name !/*CSBUG*/).ExecuteLeaves());
return(new Disposable(() =>
{
foreignKeys.ToList().ForEach(fk => SqlBuilder.AlterTableAddConstraintForeignKey(fk.ParentTable !, fk.ParentColumn !, table.Name, table.PrimaryKey.Name) !.ExecuteLeaves());
}));
}
public static bool ExistTable(Table table)
{
SchemaName schema = table.Name.Schema;
if (schema.Database != null && schema.Database.Server != null && !Database.View <SysServers>().Any(ss => ss.name == schema.Database.Server.Name))
{
return(false);
}
if (schema.Database != null && !Database.View <SysDatabases>().Any(ss => ss.name == schema.Database.Name))
{
return(false);
}
using (schema.Database == null ? null : Administrator.OverrideDatabaseInSysViews(schema.Database))
{
return((from t in Database.View <SysTables>()
join s in Database.View <SysSchemas>() on t.schema_id equals s.schema_id
where t.name == table.Name.Name && s.name == schema.Name
select t).Any());
}
}
public static Dictionary <string, DiffTable> DefaultGetDatabaseDescription(List <DatabaseName> databases)
{
List <DiffTable> allTables = new List <DiffTable>();
foreach (var db in databases)
{
using (Administrator.OverrideDatabaseInSysViews(db))
{
var tables =
(from s in Database.View <SysSchemas>()
from t in s.Tables().Where(t => !t.ExtendedProperties().Any(a => a.name == "microsoft_database_tools_support")) //IntelliSense bug
select new DiffTable
{
Name = new ObjectName(new SchemaName(db, s.name), t.name),
PrimaryKeyName = (from k in t.KeyConstraints()
where k.type == "PK"
select k.name == null ? null : new ObjectName(new SchemaName(db, k.Schema().name), k.name))
.SingleOrDefaultEx(),
Columns = (from c in t.Columns()
join userType in Database.View <SysTypes>().DefaultIfEmpty() on c.user_type_id equals userType.user_type_id
join sysType in Database.View <SysTypes>().DefaultIfEmpty() on c.system_type_id equals sysType.user_type_id
join ctr in Database.View <SysDefaultConstraints>().DefaultIfEmpty() on c.default_object_id equals ctr.object_id
select new DiffColumn
{
Name = c.name,
SqlDbType = sysType == null ? SqlDbType.Udt : ToSqlDbType(sysType.name),
UserTypeName = sysType == null ? userType.name : null,
Nullable = c.is_nullable,
Length = c.max_length,
Precission = c.precision,
Scale = c.scale,
Identity = c.is_identity,
Default = ctr.definition,
PrimaryKey = t.Indices().Any(i => i.is_primary_key && i.IndexColumns().Any(ic => ic.column_id == c.column_id)),
}).ToDictionaryEx(a => a.Name, "columns"),
MultiForeignKeys = (from fk in t.ForeignKeys()
join rt in Database.View <SysTables>() on fk.referenced_object_id equals rt.object_id
select new DiffForeignKey
{
Name = new ObjectName(new SchemaName(db, fk.Schema().name), fk.name),
IsDisabled = fk.is_disabled,
TargetTable = new ObjectName(new SchemaName(db, rt.Schema().name), rt.name),
Columns = fk.ForeignKeyColumns().Select(fkc => new DiffForeignKeyColumn
{
Parent = t.Columns().Single(c => c.column_id == fkc.parent_column_id).name,
Referenced = rt.Columns().Single(c => c.column_id == fkc.referenced_column_id).name
}).ToList(),
}).ToList(),
SimpleIndices = (from i in t.Indices()
where /*!i.is_primary_key && */ i.type != 0 /*heap indexes*/
select new DiffIndex
{
IsUnique = i.is_unique,
IsPrimary = i.is_primary_key,
IndexName = i.name,
FilterDefinition = i.filter_definition,
Type = (DiffIndexType)i.type,
Columns = (from ic in i.IndexColumns()
join c in t.Columns() on ic.column_id equals c.column_id
orderby ic.key_ordinal
select c.name).ToList()
}).ToList(),
ViewIndices = (from v in Database.View <SysViews>()
where v.name.StartsWith("VIX_" + t.name + "_")
from i in v.Indices()
select new DiffIndex
{
IsUnique = i.is_unique,
ViewName = v.name,
IndexName = i.name,
Columns = (from ic in i.IndexColumns()
join c in v.Columns() on ic.column_id equals c.column_id
orderby ic.key_ordinal
select c.name).ToList()
}).ToList(),
Stats = (from st in t.Stats()
where st.user_created
select new DiffStats
{
StatsName = st.name,
Columns = (from ic in st.StatsColumns()
join c in t.Columns() on ic.column_id equals c.column_id
select c.name).ToList()
}).ToList(),
}).ToList();
tables.ForEach(t => t.ForeignKeysToColumns());
allTables.AddRange(tables);
}
}
var database = allTables.ToDictionary(t => t.Name.ToString());
return(database);
}