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