private void GetForeignKeys() { var command = _connection.CreateCommand(); command.CommandText = @"SELECT schema_name(f.schema_id) AS [schema_name], object_name(f.parent_object_id) AS table_name, f.name AS foreign_key_name, object_schema_name(f.referenced_object_id) AS principal_table_schema_name, object_name(f.referenced_object_id) AS principal_table_name, col_name(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name, col_name(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name, is_disabled, delete_referential_action_desc, update_referential_action_desc, fc.constraint_column_id FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id ORDER BY schema_name(f.schema_id), object_name(f.parent_object_id), f.name"; using (var reader = command.ExecuteReader()) { var lastFkName = string.Empty; var lastFkSchemaName = string.Empty; var lastFkTableName = string.Empty; ForeignKeyModel fkInfo = null; while (reader.Read()) { var schemaName = reader.GetValueOrDefault <string>("schema_name"); var tableName = reader.GetValueOrDefault <string>("table_name"); var fkName = reader.GetValueOrDefault <string>("foreign_key_name"); var principalTableSchemaName = reader.GetValueOrDefault <string>("principal_table_schema_name"); var principalTableName = reader.GetValueOrDefault <string>("principal_table_name"); var fromColumnName = reader.GetValueOrDefault <string>("constraint_column_name"); var toColumnName = reader.GetValueOrDefault <string>("referenced_column_name"); var updateAction = reader.GetValueOrDefault <string>("update_referential_action_desc"); var deleteAction = reader.GetValueOrDefault <string>("delete_referential_action_desc"); var ordinal = reader.GetValueOrDefault <int>("constraint_column_id"); Logger.LogDebug( RelationalDesignEventId.FoundForeignKeyColumn, () => SqlServerDesignStrings.FoundForeignKeyColumn( schemaName, tableName, fkName, principalTableSchemaName, principalTableName, fromColumnName, toColumnName, updateAction, deleteAction, ordinal)); if (string.IsNullOrEmpty(fkName)) { Logger.LogWarning( SqlServerDesignEventId.ForeignKeyMustBeNamedWarning, () => SqlServerDesignStrings.ForeignKeyNameEmpty(schemaName, tableName)); continue; } if (!_tableSelectionSet.Allows(schemaName, tableName)) { Logger.LogDebug( SqlServerDesignEventId.ForeignKeyColumnSkipped, () => SqlServerDesignStrings.ForeignKeyColumnNotInSelectionSet( fromColumnName, fkName, schemaName, tableName)); continue; } if (fkInfo == null || lastFkSchemaName != schemaName || lastFkTableName != tableName || lastFkName != fkName) { lastFkName = fkName; lastFkSchemaName = schemaName; lastFkTableName = tableName; var table = _tables[TableKey(tableName, schemaName)]; TableModel principalTable = null; if (!string.IsNullOrEmpty(principalTableSchemaName) && !string.IsNullOrEmpty(principalTableName)) { _tables.TryGetValue(TableKey(principalTableName, principalTableSchemaName), out principalTable); } if (principalTable == null) { Logger.LogDebug( RelationalDesignEventId.ForeignKeyReferencesMissingTable, () => SqlServerDesignStrings.PrincipalTableNotInSelectionSet( fkName, schemaName, tableName, principalTableSchemaName, principalTableName)); } fkInfo = new ForeignKeyModel { Name = fkName, Table = table, PrincipalTable = principalTable, OnDelete = ConvertToReferentialAction(deleteAction) }; table.ForeignKeys.Add(fkInfo); } var fkColumn = new ForeignKeyColumnModel { Ordinal = ordinal }; ColumnModel fromColumn; if ((fromColumn = FindColumnForForeignKey(fromColumnName, fkInfo.Table, fkName)) != null) { fkColumn.Column = fromColumn; } if (fkInfo.PrincipalTable != null) { ColumnModel toColumn; if ((toColumn = FindColumnForForeignKey(toColumnName, fkInfo.PrincipalTable, fkName)) != null) { fkColumn.PrincipalColumn = toColumn; } } fkInfo.Columns.Add(fkColumn); } } }
private void GetForeignKeys() { var command = _connection.CreateCommand(); command.CommandText = @"SELECT schema_name(f.schema_id) AS [schema_name], object_name(f.parent_object_id) AS table_name, f.name AS foreign_key_name, object_schema_name(f.referenced_object_id) AS principal_table_schema_name, object_name(f.referenced_object_id) AS principal_table_name, col_name(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name, col_name(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name, is_disabled, delete_referential_action_desc, update_referential_action_desc, fc.constraint_column_id FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id ORDER BY schema_name(f.schema_id), object_name(f.parent_object_id), f.name"; using (var reader = command.ExecuteReader()) { var lastFkName = string.Empty; var lastFkSchemaName = string.Empty; var lastFkTableName = string.Empty; ForeignKeyModel fkInfo = null; while (reader.Read()) { var schemaName = reader.GetString(0); var tableName = reader.GetString(1); var fkName = reader.GetStringOrNull(2); if (string.IsNullOrEmpty(fkName)) { Logger.LogWarning(SqlServerDesignStrings.ForeignKeyNameEmpty(schemaName, tableName)); continue; } if (!_tableSelectionSet.Allows(schemaName, tableName)) { continue; } if (fkInfo == null || lastFkSchemaName != schemaName || lastFkTableName != tableName || lastFkName != fkName) { lastFkName = fkName; lastFkSchemaName = schemaName; lastFkTableName = tableName; var table = _tables[TableKey(tableName, schemaName)]; var principalSchemaTableName = reader.GetStringOrNull(3); var principalTableName = reader.GetStringOrNull(4); TableModel principalTable = null; if (!string.IsNullOrEmpty(principalSchemaTableName) && !string.IsNullOrEmpty(principalTableName)) { _tables.TryGetValue(TableKey(principalTableName, principalSchemaTableName), out principalTable); } fkInfo = new ForeignKeyModel { Name = fkName, Table = table, PrincipalTable = principalTable, OnDelete = ConvertToReferentialAction(reader.GetStringOrNull(8)) }; table.ForeignKeys.Add(fkInfo); } var fkColumn = new ForeignKeyColumnModel { Ordinal = reader.GetInt32(10) }; var fromColumnName = reader.GetStringOrNull(5); ColumnModel fromColumn; if ((fromColumn = FindColumnForForeignKey(fromColumnName, fkInfo.Table, fkName)) != null) { fkColumn.Column = fromColumn; } if (fkInfo.PrincipalTable != null) { var toColumnName = reader.GetString(6); ColumnModel toColumn; if ((toColumn = FindColumnForForeignKey(toColumnName, fkInfo.PrincipalTable, fkName)) != null) { fkColumn.PrincipalColumn = toColumn; } } fkInfo.Columns.Add(fkColumn); } } }