private bool LookupFKForColumn(string name, out FKSpecification fkSpec, List<FKSpecification> fkList)
{
fkSpec = null;
bool hasFK = false;
foreach (FKSpecification f in fkList) {
if (name.Trim().Equals(f.parentColumnName.Trim())) {
hasFK = true;
fkSpec = f;
break;
}
}
return hasFK;
}
public static List<FKSpecification> QueryForeignKeyRelationships(string connString, string tableToQuery)
{
List<FKSpecification> fkList = new List<FKSpecification>();
SqlConnection connection = null;
try
{
connection = new SqlConnection(connString);
connection.Open();
string statement1 = "SELECT f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName," +
"COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,OBJECT_NAME " +
"(f.referenced_object_id) AS ReferenceTableName, COL_NAME(fc.referenced_object_id," +
"fc.referenced_column_id) AS ReferenceColumnName FROM sys.foreign_keys AS f " +
"INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id " +
"Where OBJECT_NAME(f.parent_object_id) = \'" + tableToQuery + "\';";
SqlCommand sqc = new SqlCommand(statement1, connection);
SqlDataReader reader = sqc.ExecuteReader();
while (reader.Read())
{
string fkName = reader[0].ToString();
string TableName = reader[1].ToString();
string ColName = reader[2].ToString();
string ReferencedTable = reader[3].ToString();
string ReferencedCol = reader[4].ToString();
FKSpecification fks = new FKSpecification() { parentColumnName = ColName, parentTableName = TableName, childColumnName = ReferencedCol, childTableName = ReferencedTable };
fkList.Add(fks);
}
connection.Close();
}
catch (Exception ex)
{
}
return fkList;
}