检查表、索引空间占用

/*************************************
-- 遍历表，执行某个任务
EXEC SP_MSFOREACHTABLE "EXEC Sp_spaceused '?'"
-- 检查当前表使用的空间
EXEC SP_SPACEUSED 'Items'
*************************************/

-- 建立检查磁盘表空间的表
-- DROP TABLE zCheckTableDiskSpaces
IF OBJECT_ID('dbo.zCheckTableDiskSpaces') IS NULL
CREATE TABLE zCheckTableDiskSpaces(
Id BIGINT IDENTITY(1,1) PRIMARY KEY,
[表名称] VARCHAR(64),
[统计时间] DATETIME,
[数据行] INT,
[占用空间(MB)] DECIMAL(12,2), 
[数据使用的空间(MB)] DECIMAL(12,2),
[索引使用的空间(MB)] DECIMAL(12,2),
[保留的空间(MB)] DECIMAL(12,2),
[未使用(MB)] DECIMAL(12,2)
)
ELSE EXEC('TRUNCATE TABLE zCheckTableDiskSpaces')

GO

 

 

-- 建立检查表和索引空间的存储过程，并在每日凌晨12点之前执行一次
IF OBJECT_ID('proc_检查表和索引空间') IS NOT NULL 
DROP PROC proc_检查表和索引空间
GO

-- EXEC proc_检查表和索引空间
CREATE PROC proc_检查表和索引空间 AS
BEGIN
-- 1.建立临时表
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t(
name VARCHAR(64),rows INT,reserved VARCHAR(64),data VARCHAR(64),index_size VARCHAR(64),unused VARCHAR(64)
)
-- 2.插入表空间和数据使用情况
INSERT INTO #t EXEC SP_MSFOREACHTABLE "EXEC Sp_spaceused '?'"


-- 3.将临时表数据带到汇总表
INSERT INTO zCheckTableDiskSpaces([表名称],[统计时间],[数据行],[占用空间(MB)],[保留的空间(MB)],[数据使用的空间(MB)],[索引使用的空间(MB)],[未使用(MB)]) 
SELECT 
name AS [表名称],
GETDATE() AS [统计时间],
rows AS [数据行],
(CAST(RTRIM(REPLACE(data,'KB','')) AS DECIMAL(12,2)) + CAST(RTRIM(REPLACE(index_size,'KB','')) AS DECIMAL(12,2))) / 1024.00 AS [占用空间(MB)],
(CAST(RTRIM(REPLACE(reserved,'KB','')) AS DECIMAL(12,2))) / 1024.00 AS [保留的空间(MB)],
(CAST(RTRIM(REPLACE(data,'KB','')) AS DECIMAL(12,2))) / 1024.00 AS [数据使用的空间(MB)],
(CAST(RTRIM(REPLACE(index_size,'KB','')) AS DECIMAL(12,2))) / 1024.00 AS [索引使用的空间(MB)],
(CAST(RTRIM(REPLACE(unused,'KB','')) AS DECIMAL(12,2))) / 1024.00 AS [未使用(MB)]
FROM #t
WHERE rows > 0 OR CAST(RTRIM(REPLACE(reserved,'KB','')) AS INT) > 0
ORDER BY [占用空间(MB)] DESC

--SELECT * FROM zCheckTableDiskSpaces 
END

GO