Sample Header Ad - 728x90

Automatically detect table name in MSSQL Server database using stored function

4 votes
1 answer
2190 views
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?
Asked by Beau Smith (51 rep)
Feb 17, 2016, 05:28 PM
Last activity: Mar 19, 2024, 06:05 AM