Запросить все данные таблицы и индекса

Есть ли у кого-нибудь общий оператор SQL, который будет перечислять все таблицы и индексы в базе данных вместе с их текущим параметром сжатия для каждого раздела?

Спасибо.

EDIT: Это насколько я получил в попытке запросить таблицы, но я не уверен, что соединение правильно (я получаю дубликаты, которые, по-видимому, вызваны наличием индексов)

SELECT [t].[name], [p].[partition_number], [p].[data_compression_desc]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]

Ответ 1

Я думал, что поделюсь своим последним запросом. Это даст два набора результатов: первый для сжатия данных для куч и кластеризованных индексов, а второй для сжатия индексов для некластеризованных индексов.

SELECT [t].[name] AS [Table], [p].[partition_number] AS [Partition],
    [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
WHERE [p].[index_id] in (0,1)

SELECT [t].[name] AS [Table], [i].[name] AS [Index],  
    [p].[partition_number] AS [Partition],
    [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] ON [i].[object_id] = [p].[object_id] AND [i].[index_id] = [p].[index_id]
WHERE [p].[index_id] > 1

Ответ 2

Хотя я думаю, что, хотя окончательные запросы, опубликованные Barguast, могут работать, с ними все еще остается проблема/что-то не объяснено достаточно хорошо.

В основном index_id 0 - это куча, 1 - кластеризованный индекс, а 2 - все остальное (некластеризованные индексы).

Проблема с вышеуказанными запросами заключается в том, что запрос данных не будет работать, если таблица представляет собой кучу (даже если в таблице есть данные). Кроме того, запрос для индексов работает, потому что вы указываете index_Id = 2 и есть дуплексы из-за не соединения index_id между sys.indexes и sys.partitions. Если вы присоединитесь к ним, то в наборе результатов не будет дубликатов, и вы сможете сделать гораздо более понятный index_id not in (0,1).

В любом случае исправлены запросы ниже. Я также добавил имя индекса к первому запросу (обратите внимание, что это поле будет нулевым, если таблица представляет собой кучу). Также обратите внимание, что вам не нужно указывать соединение для index_id в первом запросе, потому что where указано (0,1) и может быть только один из них (другими словами, вы можете добавить его, если хотите, но это не так. не имеет значения).

-- Data (table) compression (heap or clustered index)
SELECT [t].[name] AS [Table], 
       [i].[name] AS [Index],
       [p].[partition_number] AS [Partition],
       [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] 
     ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] 
     ON [i].[object_id] = [p].[object_id]
WHERE [p].[index_id] in (0,1)

-- Index compression (non-clustered index)
SELECT [t].[name] AS [Table], 
       [i].[name] AS [Index],  
       [p].[partition_number] AS [Partition],
       [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] 
     ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] 
     ON [i].[object_id] = [p].[object_id] AND i.index_id = p.index_id
WHERE [p].[index_id] not in (0,1)

Ответ 3

Эти ответы все достойны и работают. Поскольку я немного приукрасил его к своей работе, я подумал, что пришло время немного пожертвовать. Этот запрос добавляет схему ответа Джейсона (который мне нужен). и он также разбирает некоторые проблемы соединения и объединяет результаты в довольно простое резюме.

-- Returns user tables and indexes in a DB and their Compression state
select s.name [Schema], t.name [Table], i.name [Index], p.data_compression_desc Compression
     , case when p.index_id in (0, 1) then 'Table' else 'Index' end CompressionObject
  from sys.tables t
  join sys.schemas s on t.schema_id = s.schema_id
  join sys.indexes i on t.object_id = i.object_id
  join sys.partitions p on (i.object_id = p.object_id and i.index_id = p.index_id)
where t.type = 'U'
order by 1, 2, p.index_id, 3

Я использовал это как "рабочий список", чтобы генерировать скрипты, чтобы сжать все, поскольку я просто поднимаю-сдвиг db в Azure VM и хочу уменьшить IOPS, чтобы улучшить perf. Надеюсь, это поможет кому-то там.

Ответ 4

Это должно выполнить задание, протестировать его для небольшого подмножества, чтобы убедиться, что оно дает вам то, что вам нужно

SELECT DISTINCT s.name [Schema], t.name [Table], i.name [Index Name], p.partition_number, p.data_compression_desc
-- uncommenting the below line will give you dupes
--, p.index_id
FROM sys.schemas s
INNER JOIN sys.tables t
    ON s.schema_id = t.schema_id
    INNER JOIN sys.indexes i
        ON t.object_id = i.object_id
    INNER JOIN sys.partitions p
        ON t.object_id = p.object_id
ORDER BY s.name, t.name

Причина, по которой вы, вероятно, обманываете, состоит в том, что у вас есть несколько записей разделов на таблицу, например. несколько index_id, см. в этой статье MSDN для уточнения того, что означает index_id. Добавление DISTINCT должно решить проблему обмана