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,设定是每天跑存储过程还是每周跑
