sqlserver数据库数据追踪例子

SELECT SYS_CHANGE_OPERATION,
t.id,t.name from  changetable(changes  [需要追踪的表明], [数据库版本号]) ct
left join [需要追踪的表明] t on   t.id = ct.id
WHERE  ct.SYS_CHANGE_VERSION<=23  

获取数据库当前版本号：

SELECT CHANGE_TRACKING_CURRENT_VERSION() as currentVersion

1.开启数据库追踪和需要追踪的表的追踪

2.开启需要追踪的表追踪

3..创建TableVersionRecord 表 用于保存追踪结果

CREATE TABLE [dbo].[TableVersionRecord](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [nvarchar](1000) NULL,
[OperationType] [nvarchar](1000) NULL,
[ChangID] [nchar](10) NULL,
[UpdateTime] [datetime] NULL CONSTRAINT [DF_TableVersionRecord_UpdateTime]  DEFAULT (getdate()),
[VersionNum] [nvarchar](1000) NULL,
 CONSTRAINT [PK_TableVersionRecord] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

4. 编写追踪存储过程：

CREATE 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