SQLserver维护

1、查询当前数据库所有用户表的数据总行数

按行数从大到小排序，输出表名 + 记录总数

拆解每张系统表

#sysindexes、sysobjects 是旧版系统视图
SELECT t2.[name],
       rows
FROM   sysindexes t1,
       sysobjects t2
WHERE  t1.id = t2.id
       AND indid < 2
ORDER  BY rows DESC

标准新版替代写法（推荐）

SELECT 
    OBJECT_NAME(p.object_id) AS 表名,
    SUM(p.rows) AS 总行数
FROM sys.partitions p
WHERE p.index_id IN (0,1)
GROUP BY p.object_id
ORDER BY SUM(p.rows) DESC;

完整加过滤优化版（只查用户表）

SELECT t2.[name], t1.rows
FROM sysindexes t1
JOIN sysobjects t2 ON t1.id = t2.id
WHERE t1.indid < 2
  AND t2.xtype = 'U' -- U=用户表，排除系统表S
ORDER BY t1.rows DESC;

2、查询碎片超过 80% 的索引

同时输出表名、碎片率、碎片块数、表数据量、一键重建索引的 DBCC 命令，按碎片严重程度从高到低排序，用于索引碎片清理优化。

SELECT 
OBJECT_NAME(ips.object_id) AS TableName,
avg_fragmentation_in_percent,
ips.fragment_count 碎片数量,
(select max( rows) from sysindexes where id =i.object_id) as 数据条数 ,
'DBCC DBREINDEX('+OBJECT_NAME(ips.object_id)+','+i.name+','+'100)' 重建索引语句,
DB_NAME(database_id) AS DatabaseName
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ips
JOIN 
    sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
	
WHERE 
    avg_fragmentation_in_percent > 80 --碎片大于80%
    AND OBJECTPROPERTY(ips.object_id,'IsUserTable') = 1 -- 过滤只查用户表，移到WHERE内
order by avg_fragmentation_in_percent desc

sysindexes 是废弃视图，新版推荐改用 sys.partitions 取行数

DBCC DBREINDEX 旧语法，官方推荐标准重建语句：

SELECT 
OBJECT_NAME(ips.object_id) AS TableName,
avg_fragmentation_in_percent,
ips.fragment_count AS 碎片数量,
(
    SELECT MAX(p.rows) 
    FROM sys.partitions p 
    WHERE p.object_id = ips.object_id AND p.index_id IN (0,1)
) AS 数据条数,
'ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(OBJECT_NAME(ips.object_id)) + ' REBUILD WITH(FILLFACTOR=100)' AS 重建索引语句,
DB_NAME(database_id) AS DatabaseName
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ips
JOIN 
    sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE 
    avg_fragmentation_in_percent > 80 --碎片大于80%
    AND OBJECTPROPERTY(ips.object_id,'IsUserTable') = 1 -- 过滤只查用户表，移到WHERE内
ORDER BY avg_fragmentation_in_percent DESC;