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