public override void GetChildRelations(Common.Entities.MetaDataSchema.Table table) { DataTable relations = new DataTable(); string query = string.Format(@"select * From (select sv.*,cu.COLUMN_NAME as ParentColumn,cu.TABLE_CATALOG as ParentCatalog,cu.TABLE_NAME as ParentTable,cu.TABLE_SCHEMA as ParentSchema From (select r.CONSTRAINT_NAME,r.UNIQUE_CONSTRAINT_CATALOG,r.UNIQUE_CONSTRAINT_NAME,r.UNIQUE_CONSTRAINT_SCHEMA,r.UPDATE_RULE,t.CONSTRAINT_CATALOG,t.CONSTRAINT_SCHEMA,t.CONSTRAINT_TYPE,t.TABLE_CATALOG,t.TABLE_NAME,t.TABLE_SCHEMA,c.COLUMN_NAME,c.ORDINAL_POSITION From {0}.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as r INNER JOIN {0}.INFORMATION_SCHEMA.TABLE_CONSTRAINTS as t on r.CONSTRAINT_NAME = t.CONSTRAINT_NAME INNER JOIN {0}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE as c on t.CONSTRAINT_NAME = c.CONSTRAINT_NAME) as sv Inner Join {0}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE as cu on sv.UNIQUE_CONSTRAINT_NAME = cu.CONSTRAINT_NAME ) as mview where ParentTable = '{2}' AND ParentSchema = '{1}' ", table.ParentDatabase.Name, table.Schema, table.Name); List <Common.Entities.MetaDataSchema.RelationShip> _ChildRelationShips = new List <Common.Entities.MetaDataSchema.RelationShip>(); relations.Load(table.ParentDatabase.ParentProject.ExtractorManager.SelectStatement(query), LoadOption.OverwriteChanges); foreach (DataRow row in relations.Rows) { Common.Entities.MetaDataSchema.RelationShip relationShip = new Entities.MetaDataSchema.RelationShip(); relationShip.Name = row["CONSTRAINT_NAME"].ToString(); relationShip.ChildTable = new Entities.MetaDataSchema.Table(row["TABLE_NAME"].ToString(), row["TABLE_SCHEMA"].ToString(), table.ParentDatabase); relationShip.ChildColumn = new Entities.MetaDataSchema.Column(row["COLUMN_NAME"].ToString()); relationShip.MasterTable = new Entities.MetaDataSchema.Table(row["ParentTable"].ToString(), row["ParentSchema"].ToString(), table.ParentDatabase); relationShip.ParentColumn = new Entities.MetaDataSchema.Column(row["ParentColumn"].ToString()); _ChildRelationShips.Add(relationShip); } table.ChildRelationShips = _ChildRelationShips; }
public override void GetChildRelations(Common.Entities.MetaDataSchema.Table table) { DataTable relations = new DataTable(); string query = string.Format(@"select * From (select sv.*,cu.COLUMN_NAME as ParentColumn,cu.TABLE_CATALOG as ParentCatalog,cu.TABLE_NAME as ParentTable,cu.TABLE_SCHEMA as ParentSchema From (select r.CONSTRAINT_NAME,r.UNIQUE_CONSTRAINT_CATALOG,r.UNIQUE_CONSTRAINT_NAME,r.UNIQUE_CONSTRAINT_SCHEMA,r.UPDATE_RULE,t.CONSTRAINT_CATALOG,t.CONSTRAINT_SCHEMA,t.CONSTRAINT_TYPE,t.TABLE_CATALOG,t.TABLE_NAME,t.TABLE_SCHEMA,c.COLUMN_NAME,c.ORDINAL_POSITION From {0}.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as r INNER JOIN {0}.INFORMATION_SCHEMA.TABLE_CONSTRAINTS as t on r.CONSTRAINT_NAME = t.CONSTRAINT_NAME INNER JOIN {0}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE as c on t.CONSTRAINT_NAME = c.CONSTRAINT_NAME) as sv Inner Join {0}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE as cu on sv.UNIQUE_CONSTRAINT_NAME = cu.CONSTRAINT_NAME ) as mview where ParentTable = '{2}' AND ParentSchema = '{1}' ", table.ParentDatabase.Name, table.Schema, table.Name); List<Common.Entities.MetaDataSchema.RelationShip> _ChildRelationShips = new List<Common.Entities.MetaDataSchema.RelationShip>(); relations.Load(table.ParentDatabase.ParentProject.ExtractorManager.SelectStatement(query), LoadOption.OverwriteChanges); foreach (DataRow row in relations.Rows) { Common.Entities.MetaDataSchema.RelationShip relationShip = new Entities.MetaDataSchema.RelationShip(); relationShip.Name = row["CONSTRAINT_NAME"].ToString(); relationShip.ChildTable = new Entities.MetaDataSchema.Table(row["TABLE_NAME"].ToString(), row["TABLE_SCHEMA"].ToString(), table.ParentDatabase); relationShip.ChildColumn = new Entities.MetaDataSchema.Column(row["COLUMN_NAME"].ToString()); relationShip.MasterTable = new Entities.MetaDataSchema.Table(row["ParentTable"].ToString(), row["ParentSchema"].ToString(), table.ParentDatabase); relationShip.ParentColumn = new Entities.MetaDataSchema.Column(row["ParentColumn"].ToString()); _ChildRelationShips.Add(relationShip); } table.ChildRelationShips = _ChildRelationShips; }