閱讀完樺仔的《分享一個SQLSERVER腳本（計算數據庫中各個表的數據量和每行記錄所占用空間）》后，我想使用文中提供的代碼做一個統計表每天的新增行數及新增存儲空間的功能

實現步驟如下：

創建表，存儲每天的表空間占用情況

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

新建作業，作業計劃每天凌晨運行一次，每天記錄表占用的空間情況，存儲到上一步建立的表中

作業中執行的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 CURSORFOR    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 _ajoin table_spaceinfo _b on _a.table_name=_b.table_name and _a.list_no=_b.list_no-1order by [時間段范圍] 

如有不對的地方，歡迎拍磚，謝謝！O(∩_∩)O