sql server记录数据库表行数变化记录

sql server记录数据库表行数变化记录
1.创建记录表

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

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]
GO

2.收集表行数变化的存储过程

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

		SELECT @version = VERSION FROM [DBO].[T_RPT_VERSION] WHERE PKID = 1
		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: 更新版本号';
		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

3.后面就可以根据自己的需求创建定时job,设定是每天跑存储过程还是每周跑