public List <ForeignKeyInfo> GetForeignKeys(string tableName)
{
List <ForeignKeyInfo> fks = new List <ForeignKeyInfo>();
string sql = @"select
a.r_constraint_name Constraint_Name,
a.table_name Sub_Table_Name,
c.column_name Sub_Table_Column_Name,
b.table_name Main_Table_Name,
d.column_name Main_Table_Column_Name
from
user_constraints a,
user_constraints b,
user_cons_columns c,
user_cons_columns d
where
a.r_constraint_name=b.constraint_name
and a.constraint_type='R'
and b.constraint_type='P'
and a.r_owner=b.owner
and a.constraint_name=c.constraint_name
and b.constraint_name=d.constraint_name
and a.owner=c.owner
and a.table_name=c.table_name
and b.owner=d.owner
and b.table_name=d.table_name and a.table_name='" + tableName + "'";
using (DbDataReader odr = OracleHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (odr.Read())
{
ForeignKeyInfo model = new ForeignKeyInfo();
if (!odr.IsDBNull(0))
{
model.ConstraintName = odr.GetString(0);
}
if (!odr.IsDBNull(1))
{
model.SubTableName = odr.GetString(1);
}
if (!odr.IsDBNull(2))
{
model.SubTableColumnName = odr.GetString(2);
}
if (!odr.IsDBNull(3))
{
model.MainTableName = odr.GetString(3);
}
if (!odr.IsDBNull(4))
{
model.MainTableColumnName = odr.GetString(4);
}
fks.Add(model);
}
}
return(fks);
}
/// <summary>
/// 获取该表下的所有外键
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public List <ForeignKeyInfo> GetForeignKeys(string tableName)
{
List <ForeignKeyInfo> fks = new List <ForeignKeyInfo>();
string sql = @"select fk.name as ConstraintName,
oMain.name as MainTableName,
MainCol.name as MainTableColumnName,
oSub.name as SubTableName,
SubCol.name as SubTableColumnName from sys.foreign_keys fk
JOIN sys.all_objects oSub
ON (fk.parent_object_id = oSub.object_id)
JOIN sys.all_objects oMain
ON (fk.referenced_object_id = oMain.object_id)
JOIN sys.foreign_key_columns fkCols
ON (fk.object_id = fkCols.constraint_object_id)
JOIN sys.columns SubCol
ON (oSub.object_id = SubCol.object_id AND fkCols.parent_column_id = SubCol.column_id)
JOIN sys.columns MainCol
ON (oMain.object_id = MainCol.object_id AND fkCols.referenced_column_id = MainCol.column_id) Where oSub.name='" + tableName + "'";
using (DbDataReader sdr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (sdr.Read())
{
ForeignKeyInfo model = new ForeignKeyInfo();
if (!sdr.IsDBNull(0))
{
model.ConstraintName = sdr.GetString(0);
}
if (!sdr.IsDBNull(1))
{
model.MainTableName = sdr.GetString(1);
}
if (!sdr.IsDBNull(2))
{
model.MainTableColumnName = sdr.GetString(2);
}
if (!sdr.IsDBNull(3))
{
model.SubTableName = sdr.GetString(3);
}
if (!sdr.IsDBNull(4))
{
model.SubTableColumnName = sdr.GetString(4);
}
fks.Add(model);
}
}
return(fks);
}