使用文中提供的代碼做一個統(tǒng)計表每天的新增行數(shù)及新增存儲空間的功能
實現(xiàn)步驟如下：
1. 創(chuàng)建表
創(chuàng)建表，存儲每天的表空間占用情況
CREATE TABLE [dbo].[t_rpt_table_spaceinfo](
[table_name] [sysname] NOT NULL,
[record_date] [date] NOT NULL,
[record_time] [time](7) NOT NULL,
[rows_count] [bigint] NULL,
[reserved] [bigint] NULL,
[data_size] [bigint] NULL,
[index_size] [bigint] NULL,
[unused] [bigint] NULL,
CONSTRAINT [PK_t_rpt_table_spaceinfo] PRIMARY KEY CLUSTERED
(
[table_name] ASC,
[record_date] ASC,
[record_time] ASC
)
)
2. 新建作業(yè)
新建作業(yè)，作業(yè)計劃每天凌晨運行一次，每天記錄表占用的空間情況，存儲到上一步建立的表中
作業(yè)中執(zhí)行的T-SQL代碼為：
SET NOCOUNT ON
/*創(chuàng)建臨時表，存放用戶表的空間及數(shù)據(jù)行數(shù)信息*/
CREATE TABLE #tablespaceinfo
(
nameinfo VARCHAR(500) ,
rowsinfo BIGINT ,
reserved VARCHAR(20) ,
datainfo VARCHAR(20) ,
index_size VARCHAR(20) ,
unused VARCHAR(20)
)
DECLARE @tablename VARCHAR(255);
/*使用游標，循環(huán)得到表空間使用情況*/
DECLARE Info_cursor CURSOR
FOR
SELECT '[' + [name] + ']'
FROM sys.tables
WHERE type = 'U';
OPEN Info_cursor
FETCH NEXT FROM Info_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tablespaceinfo
EXEC sp_spaceused @tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END
INSERT INTO t_rpt_table_spaceinfo
(record_date, record_time, [table_name], [rows_count]
, reserved, [data_size], index_size, unused)
SELECT convert(date,getdate()), convert(varchar(8),getdate(),114), nameinfo, rowsinfo
,CAST(REPLACE(reserved, 'KB', '') AS BIGINT) ,CAST(REPLACE(datainfo, 'KB', '') AS BIGINT)
,CAST(REPLACE(index_size, 'KB', '') AS BIGINT) ,CAST(REPLACE(unused, 'KB', '') AS BIGINT)
FROM #tablespaceinfo
CLOSE Info_cursor
DEALLOCATE Info_cursor
DROP TABLE [#tablespaceinfo]
3. 查詢結果
連續(xù)的數(shù)據(jù)記錄之間做比較，即可得到數(shù)據(jù)的增量變化情況
示例代碼如下：
;with table_spaceinfo as
(
select record_date, record_time, table_name, rows_count, reserved, data_size, index_size, unused
,ROW_NUMBER() over(PARTITION by table_name order by record_date,record_time asc) as list_no
from t_rpt_table_spaceinfo
)
select _a.table_name as 表名,convert(varchar(20),_a.record_date)+' '+convert(varchar(8),_a.record_time)+'~~'
+convert(varchar(20),_b.record_date)+' '+convert(varchar(8),_b.record_time) as [時間段范圍]
,_b.rows_count-_a.rows_count as [新增的行數(shù)]
,_b.data_size - _a.data_size as [新增數(shù)據(jù)空間(KB)]
from table_spaceinfo _a
join table_spaceinfo _b on _a.table_name=_b.table_name and _a.list_no=_b.list_no-1
order by [時間段范圍]
