获取数据库中的数据变化情况

最新推荐文章于 2021-07-08 23:26:56 发布

原创最新推荐文章于 2021-07-08 23:26:56 发布 · 1.8k 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了一个用于跟踪数据库表变更的存储过程实现方法，并详细展示了如何创建一个临时表来记录这些变化，同时提供了完整的SQL脚本来创建目标表，用于记录所有表的变化历史。

1.设置数据库

设置表：

ALTER PROCEDURE [dbo].[GetTableChanged]

AS

BEGIN
create table #tempTable
(
SYS_CHANGE_VERSION varchar(50),
SYS_CHANGE_OPERATION varchar(10),
ID varchar(50),
)

DECLARE @tableName NVARCHAR(100), @sql nvarchar(500),@currentVersion nvarchar(100)
DECLARE tableName_Cursor CURSOR FAST_FORWARD FOR Select Name FROM SysObjects Where XType='U' and Name !='TableVersionRecord' orDER BY Name

--SELECT @currentVersion = CHANGE_TRACKING_CURRENT_VERSION() as currentVersion

set @currentVersion =CHANGE_TRACKING_CURRENT_VERSION()

OPEN tableName_Cursor
FETCH NEXT FROM tableName_Cursor INTO @tableName;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM tableName_Cursor INTO @tableName
set @sql = 'select '''+@tableName+''' as TableName,SYS_CHANGE_OPERATION as OperationType,
ID as ChangID, getDate() as UpdateTime, '''+ @currentVersion +''' as VersionNum
from changetable(changes '+@tableName+', 0) ct'
print @sql
insert into TableVersionRecord EXEC (@sql)
END
CLOSE tableName_Cursor
DEALLOCATE tableName_Cursor
select * from #tempTable
SET NOCOUNT ON;
--exec (@sql);

END

目标表

IF OBJECT_ID ('dbo.TableVersionRecord') IS NOT NULL
DROP TABLE dbo.TableVersionRecord
GO

CREATE TABLE dbo.TableVersionRecord
(
ID INT IDENTITY NOT NULL,
TableName NVARCHAR (1000),
OperationType NVARCHAR (1000),
ChangID NCHAR (10),
UpdateTime DATETIME CONSTRAINT DF_TableVersionRecord_UpdateTime DEFAULT (getdate()),
VersionNum NVARCHAR (1000),
CONSTRAINT PK_TableVersionRecord PRIMARY KEY (ID)
)
GO