使用文中提供的代碼做一個(gè)統(tǒng)計(jì)表每天的新增行數(shù)及新增存儲(chǔ)空間的功能

實(shí)現(xiàn)步驟如下：

1. 創(chuàng)建表

創(chuàng)建表，存儲(chǔ)每天的表空間占用情況

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è)計(jì)劃每天凌晨運(yùn)行一次，每天記錄表占用的空間情況，存儲(chǔ)到上一步建立的表中

作業(yè)中執(zhí)行的T-SQL代碼為：

SET NOCOUNT ON /*創(chuàng)建臨時(shí)表，存放用戶表的空間及數(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);/*使用游標(biāo)，循環(huán)得到表空間使用情況*/ DECLARE Info_cursor CURSORFORSELECT '[' + [name] + ']'FROM sys.tablesWHERE type = 'U';OPEN Info_cursor FETCH NEXT FROM Info_cursor INTO @tablenameWHILE @@FETCH_STATUS = 0BEGIN INSERT INTO #tablespaceinfoEXEC sp_spaceused @tablename FETCH NEXT FROM Info_cursor INTO @tablename ENDINSERT 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 #tablespaceinfoCLOSE Info_cursor DEALLOCATE Info_cursor DROP TABLE [#tablespaceinfo]

3. 查詢結(jié)果

連續(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_nofrom 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 [時(shí)間段范圍],_b.rows_count-_a.rows_count as [新增的行數(shù)],_b.data_size - _a.data_size as [新增數(shù)據(jù)空間(KB)]from table_spaceinfo _ajoin table_spaceinfo _b on _a.table_name=_b.table_name and _a.list_no=_b.list_no-1order by [時(shí)間段范圍]