Ejemplo n.º 1
0
        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);
                    }
                }
            }
        }