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