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

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