Finding when a column is added

In SQL Server is there any way to get information about when columns are created or which columns are new? Also, I need to find any change in columns, for example which columns' data type changed. I can get the creation and last modification date of tables, but for columns I could not find any query to do it.

I tried using trigger, audit and other queries that I found in forums, but did not get the result I want. If someone knows the solution, help please. Third-party tools are acceptable, too. Thanks in advance. This is my trigger code ( I know it is not the best, but I did my best):

DROP TABLE IF EXISTS ColumnChanges
CREATE TABLE ColumnChanges (
Id INT IDENTITY(1,1) PRIMARY KEY,
DatabaseName NVARCHAR(100),
TableName NVARCHAR(100),
ColumnName NVARCHAR(100),
ActionType NVARCHAR(50),
OldDataType NVARCHAR(50),
NewDataType NVARCHAR(50),
Add_Modify_Date DATETIME
)


CREATE TRIGGER trg_ColumnChange_AfterAlter
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
DECLARE @EventData XML
SET @EventData = EVENTDATA()

DECLARE @DatabaseName NVARCHAR(100)
DECLARE @TableName NVARCHAR(100)
DECLARE @ActionType NVARCHAR(50)
DECLARE @NewDataType NVARCHAR(100)
DECLARE @Add_Modify_Date DATETIME

SET @DatabaseName = @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(100)')
SET @TableName = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(100)')
SET @ActionType = 'ADDED/MODIFIED'
SET @NewDataType = @EventData.value('(/EVENT_INSTANCE/AlterTableActionList/Alter/Columns/Column/DataTypeWithCollation)[1]', 'NVARCHAR(100)')
SET @Add_Modify_Date = GETDATE()

DECLARE @ColumnName NVARCHAR(100)
    SELECT @ColumnName = COLUMN_NAME    
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = @TableName

DECLARE @OldDataType NVARCHAR(100)
        SELECT @OldDataType = DATA_TYPE 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE COLUMN_NAME = @ColumnName


INSERT INTO ColumnChanges (DatabaseName, TableName, ColumnName, ActionType,OldDataType,NewDataType, Add_Modify_Date)
VALUES (@DatabaseName, @TableName, @ColumnName, @ActionType, @OldDataType, @NewDataType, @Add_Modify_Date)
  END

Solution

There are a couple of problems with your trigger. First, you cannot get the datatype from the eventdata, that information doesn't seem to be tracked. But you can get the current datatype, by querying the views. Unfortunately your code doesn't properly select correct column name.

An improved version:


CREATE OR ALTER TRIGGER trg_ColumnChange_AfterAlter
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
    DECLARE @EventData XML = EVENTDATA()
    
    INSERT INTO ColumnChanges (DatabaseName, TableName, ColumnName, ActionType,OldDataType,NewDataType, Add_Modify_Date)
    SELECT  e.value('(DatabaseName/text())[1]', 'sysname')
    ,   e.value('(ObjectName/text())[1]', 'sysname')
    ,   c.value('(Name/text())[1]', 'sysname')
    ,   c.value('local-name(..)', 'nvarchar(100)')
    ,   NULL /*not possible to get*/, sc.DATA_TYPE
    ,   e.value('(PostTime/text())[1]', 'datetime2')
    FROM    @EventData.nodes('/EVENT_INSTANCE') e(e)
    CROSS APPLY e.nodes('AlterTableActionList/*/Columns') c(c)
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS sc
           ON   sc.TABLE_NAME = e.value('(ObjectName/text())[1]', 'sysname')
           AND  sc.COLUMN_NAME = c.value('(Name/text())[1]', 'sysname')
END

This supports multiple tables and columns and both add/modify.