Automatically detect table name in MSSQL Server database using stored function

Question

I'm working on implementing Change Data Tracking in a SQL Server database and I set up a single table to test changes. In the past, changes have been made manually, but I'd like to create a way to track updates and deletions automatically through the built-in CDC process. In many cases, these changes are spread across dozens of different tables, so the ideal situation would be to create a view that collocates all of the changed data.

CDC works at the table level and doesn't track the user who makes changes. I found a way to add user data to the CDC tracking tables by creating a new user_name column and adding SUSER_SNAME() to the "Default Value or Binding" column property using the SQL command: "ALTER TABLE cdc.dbo_MyTable_CT ADD UserName nvarchar(50) NULL DEFAULT(SUSER_SNAME())" from a separate Stack Overflow question - (https://stackoverflow.com/questions/869300/sql-server-2008-change-data-capture-who-made-the-change).

I'm trying to find a similar stored function to track the table name from this list - https://msdn.microsoft.com/en-us/library/ms187812.aspx.

So far the only stored function I've successfully implemented from the list is db_name() which automatically returns the database name. I've tried schema_name and object_name, but those automatically revert to N'schema_name()' or N'object_name()' in the design view. In the table view, the changed rows return (schema_name()) or (object_name()).

Does anyone know how to automatically populate the a column with the table name of the table from which changes were made using some sort of stored function?

Answer 1

I think you would need to use a trigger on the table(s) in question in order to get the table name.

How I have implemented table change tracking is by just that: 1) Create a table to hold the changes and 2) Create a trigger on each table that needed to be tracked.

The triggers ALL look like this (where you replace 'XX_YOURTABLE_XX' with the table in question and [tr_XXXX] with a unique trigger name ):

CREATE TRIGGER [dbo].[tr_XXXX] on [dbo].[XX_YOURTABLE_XX] for INSERT, UPDATE, DELETE
AS
DECLARE 
 @bit INT ,
 @field INT ,
 @maxfield INT ,
 @char INT ,
 @fieldname VARCHAR(128) ,
 @TableName VARCHAR(128) ,
 @PKCols VARCHAR(1000) ,
 @sql VARCHAR(2000), 
 @UserName VARCHAR(128) ,
 @Type char(1) ,
 @PKSELECT VARCHAR(1000)
 SELECT @TableName = 'XX_YOURTABLE_XX'
 -- Get User
 IF object_id('tempdb..#TmpUser') IS NOT NULL
 SELECT @UserName = TheUser FROM #TmpUser
 ELSE
 SELECT @UserName = system_user
 -- Action
 IF EXISTS (SELECT * FROM INSERTED)
 IF EXISTS (SELECT * FROM DELETED)
 SELECT @Type = 'U' --UPDATE
 ELSE
 SELECT @Type = 'I' --INSERT
 ELSE
 SELECT @Type = 'D' --DELETE
 -- get lISt of columns
 SELECT * INTo #ins FROM INSERTED
 SELECT * INTo #del FROM DELETED
 -- Get primary key columns for full outer join
 SELECT @PKCols = coalesce(@PKCols + ' AND', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
 INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
 WHERE pk.TABLE_NAME = @TableName
 AND CONSTRAINT_TYPE = 'PRIMARY KEY'
 AND c.TABLE_NAME = pk.TABLE_NAME
 AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 -- Get primary key SELECT for INSERT
 SELECT @PKSELECT = coalesce(@PKSELECT+'+','') + '''<' + COLUMN_NAME + '=''+convert(VARCHAR(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>''' 
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
 INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
 WHERE pk.TABLE_NAME = @TableName
 AND CONSTRAINT_TYPE = 'PRIMARY KEY'
 AND c.TABLE_NAME = pk.TABLE_NAME
 AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 IF @PKCols IS NULL
 BEGIN
 RAISERROR('no PK on table %s', 16, -1, @TableName)
 RETURN
 END
 SELECT @field = 0, @maxfield = max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
 WHILE @field < @maxfield
 BEGIN
 SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @field
 SELECT @bit = (@field - 1 )% 8 + 1
 SELECT @bit = power(2,@bit - 1)
 SELECT @char = ((@field - 1) / 8) + 1
 IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
 BEGIN
 SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION = @field
 SELECT @sql = 'INSERT tbl_TRACKING (Type, TableName, PK, FieldName, OldValue, NewValue, UserName)'
 SELECT @sql = @sql + ' SELECT ''' + @Type + ''''
 SELECT @sql = @sql + ',''' + @TableName + ''''
 SELECT @sql = @sql + ',' + REPLACE(REPLACE(REPLACE(@PKSELECT,'<',''),'>',''),'PriKeyVal=','')
 SELECT @sql = @sql + ',''' + @fieldname + ''''
 SELECT @sql = @sql + ',convert(VARCHAR(1000),d.' + @fieldname + ')'
 SELECT @sql = @sql + ',convert(VARCHAR(1000),i.' + @fieldname + ')'
 SELECT @sql = @sql + ',''' + @UserName + ''''
 SELECT @sql = @sql + ' FROM #ins i full outer join #del d'
 SELECT @sql = @sql + @PKCols
 SELECT @sql = @sql + ' WHERE i.' + @fieldname + ' <> d.' + @fieldname 
 SELECT @sql = @sql + ' or (i.' + @fieldname + ' IS NULL AND d.' + @fieldname + ' IS not NULL)' 
 SELECT @sql = @sql + ' or (i.' + @fieldname + ' IS not NULL AND d.' + @fieldname + ' IS NULL)' 
 exec (@sql)
 END
 END
GO

The tracking table looks like this:

CREATE TABLE [dbo].[tbl_TRACKING](
 [Type] [char](1) NULL,
 [TableName] [varchar](128) NULL,
 [PK] [varchar](1000) NULL,
 [FieldName] [varchar](128) NULL,
 [OldValue] [varchar](1000) NULL,
 [NewValue] [varchar](1000) NULL,
 [ActionDate] [datetimeoffset](3) NULL,
 [UserName] [varchar](128) NULL,
 [AppName] [varchar](128) NULL,
 [ComputerName] [varchar](128) NULL
) 

Hope that helps

• Solomon Rutzky: Just FYI: You don't need to hard-code the table name in the trigger. You can use @@PROCID which returns the object_id of the current running stored procedure or trigger. That ID can be used to look in sys.objects, and the table's object_id will be listed in the parent_object_id field for the row that is the trigger. Something along the lines of: SELECT OBJECT_NAME(trg.[parent_object_id]) FROM sys.objects trg WHERE trg.[object_id] = @@PROCID;.
• Jonathan Fite: I want to point out that ChadF's solution will work for it's intended purpose, but if you do any large transactions then this will be extremely slow. When doing this through triggers there are no good options, just the best for your environment.