private void GetIndexes() { var command = _connection.CreateCommand(); command.CommandText = @"SELECT object_schema_name(i.object_id) AS [schema_name], object_name(i.object_id) AS [table_name], i.name AS [index_name], i.is_unique, c.name AS [column_name], i.type_desc, ic.key_ordinal FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id INNER JOIN sys.tables t ON t.object_id = i.object_id WHERE object_schema_name(i.object_id) <> 'sys' AND object_name(i.object_id) <> '" + HistoryRepository.DefaultTableName + @"'" + TemporalTableWhereClause + @" ORDER BY object_schema_name(i.object_id), object_name(i.object_id), i.name, ic.key_ordinal"; using (var reader = command.ExecuteReader()) { IndexModel index = null; while (reader.Read()) { var schemaName = reader.GetValueOrDefault <string>("schema_name"); var tableName = reader.GetValueOrDefault <string>("table_name"); var indexName = reader.GetValueOrDefault <string>("index_name"); var isUnique = reader.GetValueOrDefault <bool>("is_unique"); var typeDesc = reader.GetValueOrDefault <string>("type_desc"); var columnName = reader.GetValueOrDefault <string>("column_name"); var indexOrdinal = reader.GetValueOrDefault <byte>("key_ordinal"); Logger.LogDebug( RelationalDesignEventId.FoundIndexColumn, () => SqlServerDesignStrings.FoundIndexColumn( schemaName, tableName, indexName, isUnique, typeDesc, columnName, indexOrdinal)); if (!_tableSelectionSet.Allows(schemaName, tableName)) { Logger.LogDebug( RelationalDesignEventId.IndexColumnSkipped, () => SqlServerDesignStrings.IndexColumnNotInSelectionSet( columnName, indexName, schemaName, tableName)); continue; } if (string.IsNullOrEmpty(indexName)) { Logger.LogWarning( SqlServerDesignEventId.IndexMustBeNamedWarning, () => SqlServerDesignStrings.IndexNameEmpty(schemaName, tableName)); continue; } Debug.Assert(index == null || index.Table != null); if (index == null || index.Name != indexName || index.Table.Name != tableName || index.Table.SchemaName != schemaName) { TableModel table; if (!_tables.TryGetValue(TableKey(tableName, schemaName), out table)) { Logger.LogWarning( SqlServerDesignEventId.IndexTableMissingWarning, () => SqlServerDesignStrings.UnableToFindTableForIndex(indexName, schemaName, tableName)); continue; } index = new IndexModel { Table = table, Name = indexName, IsUnique = isUnique }; index.SqlServer().IsClustered = typeDesc == "CLUSTERED"; table.Indexes.Add(index); } ColumnModel column; if (string.IsNullOrEmpty(columnName)) { Logger.LogWarning( SqlServerDesignEventId.IndexColumnMustBeNamedWarning, () => SqlServerDesignStrings.ColumnNameEmptyOnIndex( schemaName, tableName, indexName)); } else if (!_tableColumns.TryGetValue(ColumnKey(index.Table, columnName), out column)) { Logger.LogWarning( RelationalDesignEventId.IndexColumnsNotMappedWarning, () => SqlServerDesignStrings.UnableToFindColumnForIndex( indexName, columnName, schemaName, tableName)); } else { var indexColumn = new IndexColumnModel { Index = index, Column = column, Ordinal = indexOrdinal }; index.IndexColumns.Add(indexColumn); } } } }
private void GetIndexes() { var command = _connection.CreateCommand(); command.CommandText = @"SELECT object_schema_name(i.object_id) AS [schema_name], object_name(i.object_id) AS [table_name], i.name AS [index_name], i.is_unique, c.name AS [column_name], i.type_desc, ic.key_ordinal FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id WHERE object_schema_name(i.object_id) <> 'sys' AND object_name(i.object_id) <> '" + HistoryRepository.DefaultTableName + @"' ORDER BY object_schema_name(i.object_id), object_name(i.object_id), i.name, ic.key_ordinal"; using (var reader = command.ExecuteReader()) { IndexModel index = null; while (reader.Read()) { var schemaName = reader.GetString(0); var tableName = reader.GetString(1); var indexName = reader.GetStringOrNull(2); if (!_tableSelectionSet.Allows(schemaName, tableName)) { continue; } if (string.IsNullOrEmpty(indexName)) { Logger.LogWarning(SqlServerDesignStrings.IndexNameEmpty(schemaName, tableName)); continue; } if (index == null || index.Name != indexName || index.Table.Name != tableName || index.Table.SchemaName != schemaName) { TableModel table; if (!_tables.TryGetValue(TableKey(tableName, schemaName), out table)) { Logger.LogWarning( SqlServerDesignStrings.UnableToFindTableForIndex(indexName, schemaName, tableName)); continue; } index = new IndexModel { Table = table, Name = indexName, IsUnique = reader.IsDBNull(3) ? false : reader.GetBoolean(3), }; index.SqlServer().IsClustered = reader.GetStringOrNull(5) == "CLUSTERED"; table.Indexes.Add(index); } var columnName = reader.GetStringOrNull(4); var indexOrdinal = reader.GetByte(6); ColumnModel column = null; if (string.IsNullOrEmpty(columnName)) { Logger.LogWarning( SqlServerDesignStrings.ColumnNameEmptyOnIndex( schemaName, tableName, indexName)); } else if (!_tableColumns.TryGetValue(ColumnKey(index.Table, columnName), out column)) { Logger.LogWarning( SqlServerDesignStrings.UnableToFindColumnForIndex( indexName, columnName, schemaName, tableName)); } else { var indexColumn = new IndexColumnModel { Index = index, Column = column, Ordinal = indexOrdinal }; index.IndexColumns.Add(indexColumn); } } } }