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