一个统计表每天的新增行数及新增存储空间的功能

使用文中提供的代码做一个统计表每天的新增行数及新增存储空间的功能

实现步骤如下：

1.创建表

创建表，存储每天的表空间占用情况

CREATETABLE[dbo].[t_rpt_table_spaceinfo](
[table_name][sysname]NOTNULL,
[record_date][date]NOTNULL,
[record_time][time](7)NOTNULL,
[rows_count][bigint]NULL,
[reserved][bigint]NULL,
[data_size][bigint]NULL,
[index_size][bigint]NULL,
[unused][bigint]NULL,
CONSTRAINT[PK_t_rpt_table_spaceinfo]PRIMARYKEYCLUSTERED
(
[table_name]ASC,
[record_date]ASC,
[record_time]ASC
)
)

2.新建作业

新建作业，作业计划每天凌晨运行一次，每天记录表占用的空间情况，存储到上一步建立的表中

作业中执行的T-SQL代码为：

SETNOCOUNTON
/*创建临时表，存放用户表的空间及数据行数信息*/
CREATETABLE#tablespaceinfo
(
nameinfoVARCHAR(500),
rowsinfoBIGINT,
reservedVARCHAR(20),
datainfoVARCHAR(20),
index_sizeVARCHAR(20),
unusedVARCHAR(20)
)

DECLARE@tablenameVARCHAR(255);

/*使用游标，循环得到表空间使用情况*/
DECLAREInfo_cursorCURSOR
FOR
SELECT"["+[name]+"]"
FROMsys.tables
WHEREtype="U";

OPENInfo_cursor
FETCHNEXTFROMInfo_cursorINTO@tablename

WHILE@@FETCH_STATUS=0
BEGIN
INSERTINTO#tablespaceinfo
EXECsp_spaceused@tablename
FETCHNEXTFROMInfo_cursor
INTO@tablename
END

INSERTINTOt_rpt_table_spaceinfo
(record_date,record_time,[table_name],[rows_count]
,reserved,[data_size],index_size,unused)
SELECTconvert(date,getdate()),convert(varchar(8),getdate(),114),nameinfo,rowsinfo
,CAST(REPLACE(reserved,"KB","")ASBIGINT),CAST(REPLACE(datainfo,"KB","")ASBIGINT)
,CAST(REPLACE(index_size,"KB","")ASBIGINT),CAST(REPLACE(unused,"KB","")ASBIGINT)
FROM#tablespaceinfo

CLOSEInfo_cursor
DEALLOCATEInfo_cursor
DROPTABLE[#tablespaceinfo]

3.查询结果

连续的数据记录之间做比较，即可得到数据的增量变化情况

示例代码如下：

;withtable_spaceinfoas
(
selectrecord_date,record_time,table_name,rows_count,reserved,data_size,index_size,unused
,ROW_NUMBER()over(PARTITIONbytable_nameorderbyrecord_date,record_timeasc)aslist_no
fromt_rpt_table_spaceinfo
)
select_a.table_nameas表名,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_countas[新增的行数]
,_b.data_size-_a.data_sizeas[新增数据空间(KB)]
fromtable_spaceinfo_a
jointable_spaceinfo_bon_a.table_name=_b.table_nameand_a.list_no=_b.list_no-1
orderby[时间段范围]