Find objects that used compression on SQL Sever

Situatie

Mai jos este o procedura ce listeaza obiectele de pe o baza de date MSSQL Server care folosesc compresie.

Solutie

ALTER procedure ObjectsWithCompression_prc

SELECT
SCHEMA_NAME([sys].objects.SCHEMA_ID) AS SchemaName
,OBJECT_NAME([sys].objects.OBJECT_ID) AS ObjectName
,(SELECT OBJECTPROPERTY(OBJECT_ID(OBJECT_NAME([sys].objects.OBJECT_ID)),’TableHasVarDecimalStorageFormat’) ) AS TableHasVarDecimalStorageFormat
,Rows
,data_compression_desc as CompressionType
,[Index_id] AS IndexIdOnTable
FROM [sys].partitions
INNER JOIN [sys].objects ON [sys].partitions.OBJECT_ID = [sys].objects.OBJECT_ID
WHERE [data_compression] > 0 AND SCHEMA_NAME([sys].objects.SCHEMA_ID) <> ‘SYS’
ORDER BY SchemaName, ObjectName

Tip solutie

Permanent

Follow Us