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