SQL Server 获取表本次更新了哪些字段以及新旧值

经查阅资料以及个人实际业务需求，编写了一个（在触发器使用）可以定位本次修改的id、修改的字段、字段新旧值，形成一个表格。
存储过程就传入一个参数：表名即可，无需带dbo等任何前缀。
update触发器中必须需将inserted和deleted做成临时表，请按照如下编写：

Select * into #deleted from deleted
Select * into #inserted from inserted
exec p_GetDiffColumns '产品表'

效果如下：

存储过程代码如下：

USE [test]
GO

ALTER proc [dbo].[p_GetDiffColumns]
(
	@TableName varchar(100)
)
as
begin
	Declare @Name nvarchar(Max)
	Declare @SQLTmp nvarchar(Max)
	Declare @RowValue nvarchar(Max)= null
	Declare @tmpTableName nvarchar(Max)
	set @tmpTableName = 'T' +
	replace(
		replace(
			replace(
				replace(
					CONVERT(nvarchar(50), GETDATE(), 21)
				, '-', '')
			, ':', '')
		, ' ', '')
	, '.', '')
	
	exec(N'select 
			cast('''' as nvarchar(max)) as ID, 
			cast('''' as nvarchar(max)) as ColName, 
			cast('''' as nvarchar(max)) as OldValue, 
			cast('''' as nvarchar(max)) as NewValue 
			into ' + @tmpTableName)

	Declare CheckColummValue Cursor for 
		Select name from sys.columns where OBJECT_ID=OBJECT_ID(@TableName)
	Open CheckColummValue
	FETCH NEXT FROM CheckColummValue INTO @Name
	WHILE @@FETCH_STATUS = 0
	BEGIN
	Set @SQLTmp=N'
	Insert into ' + @tmpTableName + ' (ID, ColName,OldValue, NewValue)
	Select a.ID, '''+@Name+''',a.'+@Name+',b.'+@Name+' From #deleted a, #inserted b Where a.id=b.id and a.'+@Name+'<>'+'b.'+@Name
	exec @RowValue=sp_executesql @SQLTmp
	FETCH NEXT FROM CheckColummValue INTO @Name
	End
	close CheckColummValue
	deallocate CheckColummValue 
	
	
	exec(N'select * from ' + @tmpTableName + ' where ColName != '''' ')
	exec(N'drop table ' + @tmpTableName)
	return
end
GO