/// <summary> /// Gets all of the foreign keys within a table. /// </summary> /// <param name="table"></param> /// <returns></returns> public virtual ForeignKeyColumnSchemaList GetForeignKeys(TableSchema table) { ForeignKeyColumnSchemaList result = new ForeignKeyColumnSchemaList(); string cmdtext = @" select distinct col_name(fc.parent_object_id, fc.parent_column_id) as column_name, c.data_type, object_name (f.referenced_object_id) as primary_key_table, c.* from sys.foreign_keys as f inner join sys.foreign_key_columns as fc on f.object_id = fc.constraint_object_id inner join information_schema.columns c on c.column_name = col_name(fc.parent_object_id, fc.parent_column_id) and c.table_name = object_name(f.parent_object_id) where object_name(f.parent_object_id) = @table_name "; using (SqlCommand cmd = new SqlCommand(cmdtext)) { cmd.Parameters.Add("@table_name", SqlDbType.NVarChar, 128).Value = table.Name; using (IDataReader reader = this._helper.ExecuteReader(cmd)) { while (reader.Read()) { string columnName = reader["column_name"].ToString(); SqlDbType sqldatatype = Enum<SqlDbType>.Parse(reader["data_type"].ToString()); Type datatype = getDataType(reader["data_type"].ToString()); Dictionary<string, object> props = new Dictionary<string, object>(); props.Add("primary_key_table", reader["primary_key_table"].ToString()); int length = reader["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value ? System.Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]) : -1; ForeignKeyColumnSchema column = new ForeignKeyColumnSchema(this, table, sqldatatype, datatype, columnName, length, props); result.Add(column); } } } return result; }
/// <summary> /// Gets all of the objects representing tables that have references to a primary key. /// </summary> /// <param name="primaryKey"></param> /// <returns></returns> public virtual ForeignKeyColumnSchemaList GetForeignKeyReferences(PrimaryKeyColumnSchema primaryKey) { ForeignKeyColumnSchemaList result = new ForeignKeyColumnSchemaList(); string cmdtext = @" select distinct col_name(fc.parent_object_id, fc.parent_column_id) as foreign_key_column, object_name(fc.parent_object_id) as foreign_key_table, col_name(fc.referenced_object_id, fc.referenced_column_id) as primary_key_column, object_name(fc.referenced_object_id) as primary_key_table, c.data_type, c.character_maximum_length, c.is_nullable, c.table_name, c.column_name from sys.foreign_keys as f inner join sys.foreign_key_columns as fc on f.object_id = fc.constraint_object_id inner join information_schema.columns c on c.column_name = col_name(fc.parent_object_id, fc.parent_column_id) and c.table_name = object_name(f.parent_object_id) where object_name (f.referenced_object_id) = @table_name "; using (SqlCommand cmd = new SqlCommand(cmdtext)) { cmd.Parameters.Add("@table_name", SqlDbType.NVarChar, 128).Value = primaryKey.Table.Name; using (IDataReader reader = this._helper.ExecuteReader(cmd)) { while (reader.Read()) { string tableName = reader["TABLE_NAME"].ToString(); SqlDbType sqlType = Enum<SqlDbType>.Parse(reader["DATA_TYPE"].ToString()); Type dataType = getDataType(reader["DATA_TYPE"].ToString()); string name = reader["COLUMN_NAME"].ToString(); Dictionary<string, object> props = new Dictionary<string, object>(); props.Add("is_identity", false); int length = reader["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value ? System.Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]) : -1; props.Add("is_primary_key", false); props.Add("is_foreign_key", true); props.Add("primary_key_table", reader["primary_key_table"]); props.Add("primary_key_column", reader["primary_key_column"]); props.Add("foreign_key_column", reader["foreign_key_column"]); props.Add("foreign_key_table", reader["foreign_key_table"]); props.Add("is_nullable", (reader["is_nullable"].ToString().Equals("YES"))); result.Add(new ForeignKeyColumnSchema(this, new TableSchema(this, tableName), sqlType, dataType, name, length, props)); } } } return result; }