public override List<SchemaForeignKeyColumn> GetForeignKeyColumns(string owner, string tableName) { using (DbConnectionInfo cn = Db.CreateConnection()) { using (DbCommand cm = cn.CreateCommand()) { string sql = String.Format(@"SELECT c.constraint_name, c.owner AS table_owner, c.table_name AS table_name, ctc.column_name, p.constraint_name AS referenced_constraint_name, p.owner AS referenced_table_owner, p.table_name AS referenced_table_name, ptc.column_name AS referenced_column_name FROM all_tab_columns ptc, all_tab_columns ctc, all_cons_columns pcc, all_cons_columns ccc, all_constraints p, all_constraints c WHERE c.constraint_type = 'R' AND p.constraint_name = c.r_constraint_name AND p.owner = c.r_owner AND ccc.owner = c.owner AND ccc.constraint_name = c.constraint_name AND ctc.owner = c.owner AND ctc.table_name = ccc.table_name AND ctc.column_name = ccc.column_name AND pcc.owner = c.r_owner AND pcc.constraint_name = p.constraint_name AND ptc.owner = c.r_owner AND ptc.table_name = pcc.table_name AND ptc.column_name = pcc.column_name AND ccc.POSITION = pcc.POSITION AND (c.owner = {0} or {0}='NULL') AND (c.table_name = {1} or {1}='NULL') ORDER BY c.owner, c.table_name, c.constraint_name, ctc.column_name", Db.GetParameterMarker("p_owner"), Db.GetParameterMarker("p_tablename")); cm.CommandText = sql; cm.Parameters.Add(Db.CreateParameter("p_owner", owner.IfNull("NULL").ToUpper())); cm.Parameters.Add(Db.CreateParameter("p_tablename", tableName.IfNull("NULL").ToUpper())); DataTable dt = Db.ExecQuery(cm); DataRowCollection drc = dt.Rows; List<SchemaForeignKeyColumn> list = new List<SchemaForeignKeyColumn>(drc.Count); for (int i = 0; i < drc.Count; i++) { SchemaForeignKeyColumn item = new SchemaForeignKeyColumn(); item.Owner = drc[i]["TABLE_OWNER"].IfNull(String.Empty); item.ConstraintName = drc[i]["CONSTRAINT_NAME"].IfNull(String.Empty); item.TableName = drc[i]["TABLE_NAME"].IfNull(String.Empty); item.ColumnName = drc[i]["COLUMN_NAME"].IfNull(String.Empty); item.ReferencedTableName = drc[i]["REFERENCED_TABLE_NAME"].IfNull(String.Empty); item.ReferencedColumnName = drc[i]["REFERENCED_COLUMN_NAME"].IfNull(String.Empty); list.Add(item); } return list; } } }
public override List<SchemaForeignKeyColumn> GetForeignKeyColumns(string owner, string tableName) { using (DbConnectionInfo cn = Db.CreateConnection()) { using (DbCommand cm = cn.CreateCommand()) { string sql = String.Format(@"SELECT CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME, TABLE_CATALOG = FK.TABLE_CATALOG, TABLE_SCHEMA = FK.TABLE_SCHEMA, TABLE_NAME = FK.TABLE_NAME, COLUMN_NAME = FK_COLS.COLUMN_NAME, REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG, REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA, REFERENCED_TABLE_NAME = PK.TABLE_NAME, REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME AND FK.CONSTRAINT_TYPE = 'FOREIGN KEY' INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY' INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME AND FK_COLS.ORDINAL_POSITION = PK_COLS.ORDINAL_POSITION WHERE FK.TABLE_CATALOG={0} AND (FK.TABLE_SCHEMA={1} OR {1}='NULL') AND (FK.TABLE_NAME={2} OR {2}='NULL') ORDER BY FK.TABLE_CATALOG, FK.TABLE_SCHEMA, FK.TABLE_NAME, REF_CONST.CONSTRAINT_NAME, FK_COLS.COLUMN_NAME", Db.GetParameterMarker("database"), Db.GetParameterMarker("owner"), Db.GetParameterMarker("tablename")); cm.CommandText = sql; cm.Parameters.Add(Db.CreateParameter("database", cn.Database)); cm.Parameters.Add(Db.CreateParameter("owner", owner.IfNull("NULL"))); cm.Parameters.Add(Db.CreateParameter("tablename", tableName.IfNull("NULL"))); DataTable dt = Db.ExecQuery(cm); DataRowCollection drc = dt.Rows; List<SchemaForeignKeyColumn> list = new List<SchemaForeignKeyColumn>(drc.Count); for (int i = 0; i < drc.Count; i++) { SchemaForeignKeyColumn item = new SchemaForeignKeyColumn(); item.Owner = drc[i]["TABLE_SCHEMA"].IfNull(String.Empty); item.ConstraintName = drc[i]["CONSTRAINT_NAME"].IfNull(String.Empty); item.TableName = drc[i]["TABLE_NAME"].IfNull(String.Empty); item.ColumnName = drc[i]["COLUMN_NAME"].IfNull(String.Empty); item.ReferencedTableName = drc[i]["REFERENCED_TABLE_NAME"].IfNull(String.Empty); item.ReferencedColumnName = drc[i]["REFERENCED_COLUMN_NAME"].IfNull(String.Empty); list.Add(item); } return list; } } }