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