How can I get updated column names from different tables and insert their data with column name in a single history table in SQL Server?

I have a history table like below

CREATE TABLE [dbo].[t_PettyCash_History] 
(
    [TableName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ColumnName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PreviousValue] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CurrentValue] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [UpdatedBy] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [UpdateDate] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
)

What I am trying to do is to write a trigger or stored_procedure which will do the below

  • Efficiently select nested dependency tables and multiple columns in a single row using join in SQL server
  • combine 2 varchar column's data and convert to datetime
  • Returning multiple tables from a stored procedure
  • Pulling data from Azure SQL database into an Excel through a parameterised stored procedure
  • Update and check vs Select with holdlock
  • SqlNotificationEvents with signalR causes to fire multiple times?
    1. Select the name of the table which is updated
    2. Select the name of the column in which data is inserted, updated or deleted
    3. then insert the values in above table similar like

      TableName  ColumnName  PreviousValue   CurrentValue  UpdatedBy   UpdateDate
      t_Table1        Col1        XXX             YYY        A       '20-Mar-2012'
      

    Thanks in advance !

    One Solution collect form web for “How can I get updated column names from different tables and insert their data with column name in a single history table in SQL Server?”

    Being agreed with Karl, currently using individual history table to keep track the changes of each tables. That is, created history table for each table and wrote triggers on main tables for insert, update, and delete action.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.