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

实现步骤如下:

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 [时间段范围]

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注