Situatie
Mai jos esteun script care listeaza tabelele de pe o baza MSSQL Server, iar pentru acestea listeaza mai multe informatii, precum daca tabelele sau indexii acestora au compresie sau nu.
Solutie
SELECT [s].[name] AS ‘schema’,
[o].[name] AS ‘table’,
CASE [o].[type]
WHEN ‘v’
THEN ‘View’
WHEN ‘u’
THEN ‘Table’
ELSE [o].[type]
END AS [objecttype],
[i].object_id,
[i].[name] AS [indexname],
[i].[index_id],
[i].[type],
[i].[type_desc],
[p].[data_compression_desc],
[p].[rows],
[i].[is_unique],
[i].[data_space_id],
[i].[ignore_dup_key],
[i].[is_primary_key],
[i].[is_unique_constraint],
[i].[fill_factor],
[i].[is_padded],
[i].[is_disabled],
[i].[is_hypothetical],
[i].[allow_row_locks],
[i].[allow_page_locks],
[i].[has_filter],
[i].[filter_definition],
CASE
WHEN [ps].[usedpages] > [ps].[pages]
THEN
(
[ps].[usedpages] – [ps].[pages]
)
ELSE 0
END * 8 AS [indexsizekb],
CASE
WHEN [ps].[usedpages] > [ps].[pages]
THEN
(
[ps].[usedpages] – [ps].[pages]
)
ELSE 0
END * 8 / 1024 AS [indexsizemb]
FROM [sys].[indexes] AS [i]
INNER JOIN [sys].[objects] AS [o] ON [o].object_id = [i].object_id
LEFT JOIN [sys].[schemas] AS [s] ON [o].schema_id = [s].schema_id
LEFT JOIN [sys].[partitions] AS [p] ON [i].[index_id] = [p].[index_id]
AND [i].object_id = [p].object_id
INNER JOIN
(
SELECT object_id,
[index_id],
SUM([used_page_count]) AS [usedpages],
SUM(CASE
WHEN([index_id] < 2)
THEN
(
[in_row_data_page_count] + [lob_used_page_count] + [row_overflow_used_page_count]
)
ELSE
[lob_used_page_count] + [row_overflow_used_page_count]
END) AS [pages]
FROM [sys].[dm_db_partition_stats]
GROUP BY object_id,
[index_id]
) AS [ps] ON [i].[index_id] = [ps].[index_id]
AND [i].object_id = [ps].object_id
WHERE 1 = 1
AND [o].[type] <> ‘s’
Leave A Comment?