Uncategorized

Compressed Tables List

9. 3. 2018
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT
[t].[name] [TableName],
[i1].[name] [IndexName],
[i1].[type_desc] [IndexType],
[fg].[name] AS [FileGroupName],
[p].[partition_number] AS [PartitionNumber],
CAST([p].[rows] AS FLOAT) AS [RowCount],
[p].[data_compression] AS [DataCompression],
[p].[data_compression_desc] [DataCompressionDesc]
FROM [sys].[tables] AS [t]
INNER JOIN [sys].[indexes] AS [i1] ON [i1].[object_id] = [t].[object_id]
INNER JOIN [sys].[partitions] AS [p] ON [p].[object_id]=CAST([t].[object_id] AS INT) AND [p].[index_id]=[i1].[index_id]
INNER JOIN [sys].[indexes] AS [i2] ON [p].[object_id] = [i2].[object_id] AND [p].[index_id] = [i2].[index_id]
LEFT OUTER JOIN [sys].[destination_data_spaces] AS [dds] ON [dds].[partition_scheme_id] = [i2].[data_space_id] AND [dds].[destination_id] = [p].[partition_number]
LEFT OUTER JOIN [sys].[partition_schemes] AS [ps] ON [ps].[data_space_id] = [i2].[data_space_id]
LEFT OUTER JOIN [sys].[partition_range_values] AS [prv] ON [prv].[boundary_id] = [p].[partition_number] AND [prv].[function_id] = [ps].[function_id]
LEFT OUTER JOIN [sys].[filegroups] AS [fg] ON [fg].[data_space_id] = [dds].[data_space_id] OR [fg].[data_space_id] = [i2].[data_space_id]
WHERE [p].[data_compression] <> 0
ORDER BY [TableName], [IndexName], [PartitionNumber] ASC
GO
SELECT [t].[name] [TableName], [i1].[name] [IndexName], [i1].[type_desc] [IndexType], [fg].[name] AS [FileGroupName], [p].[partition_number] AS [PartitionNumber], CAST([p].[rows] AS FLOAT) AS [RowCount], [p].[data_compression] AS [DataCompression], [p].[data_compression_desc] [DataCompressionDesc] FROM [sys].[tables] AS [t] INNER JOIN [sys].[indexes] AS [i1] ON [i1].[object_id] = [t].[object_id] INNER JOIN [sys].[partitions] AS [p] ON [p].[object_id]=CAST([t].[object_id] AS INT) AND [p].[index_id]=[i1].[index_id] INNER JOIN [sys].[indexes] AS [i2] ON [p].[object_id] = [i2].[object_id] AND [p].[index_id] = [i2].[index_id] LEFT OUTER JOIN [sys].[destination_data_spaces] AS [dds] ON [dds].[partition_scheme_id] = [i2].[data_space_id] AND [dds].[destination_id] = [p].[partition_number] LEFT OUTER JOIN [sys].[partition_schemes] AS [ps] ON [ps].[data_space_id] = [i2].[data_space_id] LEFT OUTER JOIN [sys].[partition_range_values] AS [prv] ON [prv].[boundary_id] = [p].[partition_number] AND [prv].[function_id] = [ps].[function_id] LEFT OUTER JOIN [sys].[filegroups] AS [fg] ON [fg].[data_space_id] = [dds].[data_space_id] OR [fg].[data_space_id] = [i2].[data_space_id] WHERE [p].[data_compression] <> 0 ORDER BY [TableName], [IndexName], [PartitionNumber] ASC GO
SELECT
	[t].[name] [TableName],
	[i1].[name] [IndexName],
	[i1].[type_desc] [IndexType],
	[fg].[name] AS [FileGroupName],
	[p].[partition_number] AS [PartitionNumber],
	CAST([p].[rows] AS FLOAT) AS [RowCount],
	[p].[data_compression] AS [DataCompression],
	[p].[data_compression_desc] [DataCompressionDesc]
FROM [sys].[tables] AS [t]
	INNER JOIN [sys].[indexes] AS [i1] ON [i1].[object_id] = [t].[object_id]
	INNER JOIN [sys].[partitions] AS [p] ON [p].[object_id]=CAST([t].[object_id] AS INT) AND [p].[index_id]=[i1].[index_id]
	INNER JOIN [sys].[indexes] AS [i2] ON [p].[object_id] = [i2].[object_id] AND [p].[index_id] = [i2].[index_id]
	LEFT OUTER JOIN [sys].[destination_data_spaces] AS [dds] ON [dds].[partition_scheme_id] = [i2].[data_space_id] AND [dds].[destination_id] = [p].[partition_number]
	LEFT OUTER JOIN [sys].[partition_schemes] AS [ps] ON [ps].[data_space_id] = [i2].[data_space_id]
	LEFT OUTER JOIN [sys].[partition_range_values] AS [prv] ON [prv].[boundary_id] = [p].[partition_number] AND [prv].[function_id] = [ps].[function_id]
	LEFT OUTER JOIN [sys].[filegroups] AS [fg] ON  [fg].[data_space_id] = [dds].[data_space_id] OR [fg].[data_space_id] = [i2].[data_space_id]
WHERE [p].[data_compression] <> 0
ORDER BY [TableName], [IndexName], [PartitionNumber] ASC
GO
89
Share: