一、创建数据表空间详情记录表

CREATE TABLE [dbo].[T_RPT_TABLE_SPACEINFO](
	[PKID] [bigint] IDENTITY(1,1) NOT NULL,
	[TB_NAME] [varchar](512) NULL,
	[CREATE_DATE] [datetime] NULL,
	[ROWS] [bigint] NULL,
	[INCREASE] [bigint] NULL,
	[VERSION] [bigint] NULL,
	[SCHEMA_NAMES] [varchar](512) NULL,
	[WEEK_NUM] [int] NULL,
 CONSTRAINT [PK_T_RPT_TABLE] PRIMARY KEY CLUSTERED 
(
	[PKID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

二、创建执行版本表

CREATE TABLE [dbo].[T_RPT_VERSION](
    [VERSION] [int] NOT NULL,
    
) ON [PRIMARY]

三、创建存储过程

alter PROCEDURE [dbo].[report_database_table_spaceinfo]
AS
BEGIN
    BEGIN TRY
		DECLARE @version INTEGER;
		DECLARE @new_version INTEGER;  
		PRINT 'step1: 查询本次操作的版本号';

		SELECT @version = isnull(max(VERSION),0) FROM [DBO].[T_RPT_VERSION] 
		PRINT @version;
		SET @new_version = @version + 1;


        PRINT 'step2: 查询出所有表和条数';
	
		/*使用游标，循环得到表空间使用情况*/ 
		DECLARE Info_cursor CURSOR
		FOR
           SELECT A.NAME AS table_names ,B.ROWS AS table_rows, C.name AS schema_names FROM sysobjects  A JOIN sysindexes B ON A.id = B.id LEFT JOIN sys.schemas c ON A.uid = C.schema_id WHERE A.xtype = 'U' AND B.indid IN(0,1)  ORDER BY B.ROWS DESC
		OPEN Info_cursor  
		DECLARE @table_names VARCHAR(512), @table_rows VARCHAR(512), @schema_names VARCHAR(512);  ;    
		FETCH NEXT FROM Info_cursor INTO @table_names,  @table_rows, @schema_names
		

		WHILE @@FETCH_STATUS = 0
		BEGIN 
			DECLARE @rows INTEGER;
			SET @rows = 0;
			SELECT @rows = ROWS FROM [DBO].[T_RPT_TABLE_SPACEINFO] WHERE TB_NAME = @table_names AND VERSION = @version AND SCHEMA_NAMES = @schema_names;
			
			/*插入数据*/
			INSERT INTO [dbo].[T_RPT_TABLE_SPACEINFO]
			(
				[SCHEMA_NAMES]
			   ,[TB_NAME]
			   ,[CREATE_DATE]
			   ,[ROWS]
			   ,[INCREASE]
			   ,[VERSION]
			   ,[WEEK_NUM]

			)
			VALUES
           (
		        @schema_names
			   ,@table_names
			   ,GETDATE()
			   ,@table_rows
			   ,@table_rows - @rows
			   ,@new_version
			   ,DATENAME(WEEK, GETDATE())
		   )
			FETCH NEXT FROM Info_cursor INTO @table_names,  @table_rows, @schema_names

		END
		CLOSE Info_cursor  
		DEALLOCATE Info_cursor  
		PRINT 'step3: 更新版本号';
		insert into T_RPT_VERSION (VERSION)
		select @new_version
		--UPDATE [DBO].[T_RPT_VERSION] SET VERSION = @new_version WHERE PKID = 1;

    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN;
            PRINT '数据回滚';
        END;
        PRINT '失败';
    END CATCH;
    PRINT '操作完成';
END;

GO

四、运行统计分析

1、执行存储过程。记录期初总行
exec report_database_table_spaceinfo
2、业务系统操作（略）
3、重新执行存储过程，记录最近的数据总行
exec report_database_table_spaceinfo
4、查看最近一次总行数与最近一次的差异
WITH MaxVersion AS (
    SELECT MAX(VERSION) AS MaxVer
    FROM [DBO].[T_RPT_VERSION]
),
RankedData AS (
    SELECT *,
           LAG(version, 1) OVER (PARTITION BY TB_NAME ORDER BY create_date) AS preVersion,
           LAG(rows, 1) OVER (PARTITION BY TB_NAME ORDER BY create_date) AS preRows
    FROM T_RPT_TABLE_SPACEINFO
)
SELECT *
FROM RankedData
WHERE ROWS <> preRows
  AND version = (SELECT MaxVer FROM MaxVersion);

完美手工！