如何修改MSSQL表统计查询语句以指定单表并获取更多统计信息

没问题，我帮你把原查询改成针对单表的版本，同时给你一些获取更多表统计信息的实用方法👇

直接在原查询基础上添加WHERE子句过滤目标表即可，这里建议同时指定架构名（避免不同架构下同名表的歧义）：

USE [mydbname]
GO
SELECT 
    s.Name AS SchemaName,
    t.Name AS TableName,
    p.rows AS RowCounts,
    CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
    CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
    CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
-- 过滤指定表和架构
WHERE s.Name = N'YourSchemaName' 
  AND t.Name = N'YourTableName'
GROUP BY t.Name, s.Name, p.Rows
ORDER BY s.Name, t.Name
GO

提示：把[mydbname]、YourSchemaName、YourTableName替换成你实际的数据库名、架构名和表名即可。如果你的表默认在dbo架构下，架构名填dbo就行。

除了基础的行数和空间，你还可以查询这些更细节的统计信息：

1. 用系统存储过程快速查看空间（最简单） #

sp_spaceused是SQL Server自带的存储过程，返回的信息更简洁，还包含索引总大小：

USE [mydbname]
GO
EXEC sp_spaceused N'YourSchemaName.YourTableName';
GO

2. 查询索引的空间与碎片情况 #

如果想知道每个索引占用的空间，以及索引碎片率（用于优化查询性能），可以用这个查询：

USE [mydbname]
GO
SELECT
    s.Name AS SchemaName,
    t.Name AS TableName,
    i.Name AS IndexName,
    i.type_desc AS IndexType,
    CAST(ROUND((SUM(a.used_pages)/128.00),2) AS NUMERIC(36,2)) AS IndexUsed_MB,
    -- 计算索引碎片率（仅适用于非聚集索引）
    ROUND(ips.avg_fragmentation_in_percent, 2) AS Fragmentation_Percent
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), t.object_id, i.index_id, NULL, 'DETAILED') ips
WHERE s.Name = N'YourSchemaName' 
  AND t.Name = N'YourTableName'
GROUP BY s.Name, t.Name, i.Name, i.type_desc, ips.avg_fragmentation_in_percent
ORDER BY Fragmentation_Percent DESC;
GO

3. 查看表的列统计信息 #

了解SQL Server为表创建的统计信息（用于查询优化器生成执行计划）：

USE [mydbname]
GO
SELECT
    s.Name AS SchemaName,
    t.Name AS TableName,
    st.Name AS StatisticName,
    sc.Name AS ColumnName,
    st.auto_created AS IsAutoCreated,
    st.last_updated AS LastUpdated
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.stats st ON t.object_id = st.object_id
INNER JOIN sys.stats_columns sc ON st.object_id = sc.object_id AND st.stats_id = sc.stats_id
INNER JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
WHERE s.Name = N'YourSchemaName' 
  AND t.Name = N'YourTableName'
ORDER BY st.last_updated DESC;
GO

4. 分区表的分区统计（如果表有分区） #

如果你的表是分区表，可以查看每个分区的行数和空间占用：

USE [mydbname]
GO
SELECT
    s.Name AS SchemaName,
    t.Name AS TableName,
    p.partition_number AS PartitionNumber,
    p.rows AS PartitionRowCounts,
    CAST(ROUND((SUM(a.used_pages)/128.00),2) AS NUMERIC(36,2)) AS PartitionUsed_MB,
    CAST(ROUND((SUM(a.total_pages)/128.00),2) AS NUMERIC(36,2)) AS PartitionTotal_MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.Name = N'YourSchemaName' 
  AND t.Name = N'YourTableName'
GROUP BY s.Name, t.Name, p.partition_number, p.rows
ORDER BY p.partition_number;
GO

内容的提问来源于stack exchange，提问作者600vka