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

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

2020-04-02 15:24whsnow Sql Server

这篇文章主要介绍了一个统计表每天的新增行数及新增存储空间的功能,需要的朋友可以参考下

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

实现步骤如下：

1. 创建表

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

				?

									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. 新建作业

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

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

				?

									SET NOCOUNT ON

									/*创建临时表，存放用户表的空间及数据行数信息*/

									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); 

									/*使用游标，循环得到表空间使用情况*/ 

									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. 查询结果

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

示例代码如下：

				?

									;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 [新增的行数]

									,_b.data_size - _a.data_size as [新增数据空间(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 [时间段范围]