--查看數據庫中所有索引的碎片信息
use ${數據庫名}
DBCC SHOWCONTIG WITH ALL_INDEXES 
--查看指定表的所有索引的碎片信息
DBCC SHOWCONTIG (${表名}) WITH ALL_INDEXES   
--查看指定表、指定索引的碎片信息
DBCC SHOWCONTIG (${表名},${索引名})

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N'db1'), OBJECT_ID(N'db1.dbo.users'), NULL, NULL , 'LIMITED');
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N'db1'), OBJECT_ID(N'db1.dbo.users'), NULL, NULL , 'DETAILED');

SELECT 
　　 db_name() as DbName,
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 總共占用空間MB,
    SUM(a.used_pages) * 8 AS 總使用空間KB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 總使用空間MB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS 碎片化空間KB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS 碎片化空間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
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.is_ms_shipped = 0
    AND i.OBJECT_ID > 0
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    總共占用空間MB desc

CREATE INDEX ${index_name} ON T(${index_col})  WITH (DROP_EXISTING = ON)  

-- 重建指定索引
USE ${db_name}; ??
GO ?
DBCC DBREINDEX ('${schema_name}.${table_name}', ${index_name},80); ?
GO

-- 重建指定表全部索引
USE ${db_name}; ??
GO ?
DBCC DBREINDEX ('${schema_name}.${table_name}', ' ', 70); ?
GO

DBCC INDEXDEFRAG (${db_name}, '${schema_name}.${table_name}', ${index_name});  

-- 檢查數據庫文件空間使用率
SELECT a.name [文件名稱] ,cast(a.[size]*1.0/128 as decimal(12,1)) AS [文件設置大小(MB)] ,
    CAST( fileproperty(s.name,'SpaceUsed')/(8*16.0) AS DECIMAL(12,1)) AS [文件所占空間(MB)] ,
    CAST( (fileproperty(s.name,'SpaceUsed')/(8*16.0))/(s.size/(8*16.0))*100.0 AS DECIMAL(12,1)) AS [所占空間率%] ,
    CASE WHEN A.growth =0 THEN '文件大小固定，不會增長' ELSE '文件將自動增長' end [增長模式] ,CASE WHEN A.growth > 0 AND is_percent_growth = 0 
    THEN '增量為固定大小' WHEN A.growth > 0 AND is_percent_growth = 1 THEN '增量將用整數百分比表示' ELSE '文件大小固定，不會增長' END AS [增量模式] ,
    CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN cast(cast(a.growth*1.0/128as decimal(12,0)) AS VARCHAR)+'MB' 
    WHEN A.growth > 0 AND is_percent_growth = 1 THEN cast(cast(a.growth AS decimal(12,0)) AS VARCHAR)+'%' ELSE '文件大小固定，不會增長' end AS [增長值(%或MB)] ,
    a.physical_name AS [文件所在目錄] ,a.type_desc AS [文件類型] 
FROM sys.database_files a 
INNER JOIN sys.sysfiles AS s  ON a.[file_id]=s.fileid 
LEFT JOIN sys.dm_db_file_space_usage b ON a.[file_id]=b.[file_id] ORDER BY a.[type]

USE [${db_name}]
GO
DBCC SHRINKDATABASE(N'${db_name}' )
GO