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

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

 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]

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