mssql 常用sql收集

由于MSSQL企业版数据库切换到标准版数据库,不支持 压缩和分区表,所以我们需要将采用分区表和压缩的表单独挑出来,进行调整,
所以特收集以下语句,备以后使用

查看系统那些表采用分区表

SELECT t.object_id AS Object_ID, t.name AS TableName, ic.column_id
as PartitioningColumnID, c.name AS PartitioningColumnName
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.columns AS c
ON t.object_id = c.object_id
JOIN sys.partition_schemes AS ps
ON ps.data_space_id = i.data_space_id
JOIN sys.index_columns AS ic
ON ic.object_id = i.object_id AND ic.index_id = i.index_id
AND ic.partition_ordinal > 0


查看系统那些表采用了 压缩模式(行压缩 页压缩)

SELECT DISTINCT
SCHEMA_NAME(o.schema_id) + ‘.’ + OBJECT_NAME(o.object_id) AS TableName,
i.name AS IndexName,
p.data_compression_desc AS CompressionType,
i.type_desc AS StorageType
FROM sys.partitions p with(nolock)
INNER JOIN sys.objects o with(nolock)
ON p.object_id = o.object_id
JOIN sys.indexes i
ON p.object_id = i.object_id
AND i.index_id = p.index_id
WHERE p.data_compression > 0
AND SCHEMA_NAME(o.schema_id) <> ‘SYS’