Maintaining DDL Trigger log for alter table

Question

Table to maintain DDL log data

CREATE TABLE DDLAudit_All (
    AuditID INT IDENTITY PRIMARY KEY,
    DatabaseName varchar(256), 
    ActionOn varchar(100),
    Event nvarchar(100), 
    ObjectName varchar(256), 
    TSQL nvarchar(2000), 
    Login varchar(256),
    EventData XML NOT NULL,
    CreatedDate datetime
);
GO

Create DDL trigger on Database

CREATE TRIGGER DDL_ALL_Events
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS

DECLARE @ed XML
SET @ed = EVENTDATA()

INSERT INTO DDLAudit_All (DatabaseName, ActionOn, Event, ObjectName, TSQL, Login,EventData,CreatedDate) 
VALUES
(

    @ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
    @ed.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(100)'),
    @ed.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
    @ed.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
    @ed.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
    @ed.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)'),
    EventData(),
    GetDate()
) ;

Create table Scritp

CREATE TABLE EmployeeTable_DDL
(  
    Emp_ID INT Identity,  
    Emp_name Varchar(100),  
    Emp_Sal Decimal (10,2) 
)

One record inserted in DDLAudit_All table like below

+--------------+----------+--------------+-------------------+----------------------------------------------------------------------------------------------------------------------+
| DatabaseName | ActionOn |    Event     |    ObjectName     |                                                         TSQL                                                         |
+--------------+----------+--------------+-------------------+----------------------------------------------------------------------------------------------------------------------+
| Pratice_DB   | TABLE    | CREATE_TABLE | EmployeeTable_DDL | CREATE TABLE EmployeeTable_DDL  (     Emp_ID INT Identity,     Emp_name Varchar(100),     Emp_Sal Decimal (10,2)   ) |
+--------------+----------+--------------+-------------------+----------------------------------------------------------------------------------------------------------------------+

Executed T-SQL statement to change data type length like below

alter table EmployeeTable_DDL alter column Emp_name varchar(200);

One record inserted in DDLAudit_All table after executing above alter statement like below

+--------------+----------+---------------------+--------------------+------------------------------------------------------------------+
| DatabaseName | ActionOn |        Event        |         ObjectName |                                   TSQL                           |
+--------------+----------+---------------------+--------------------+------------------------------------------------------------------+
| Pratice_DB   | TABLE    |         ALTER_TABLE | EmployeeTable_DDL  | alter table EmployeeTable_DDL alter column Emp_name varchar(200) |
+--------------+----------+---------------------+--------------------+------------------------------------------------------------------+

Changed the data type length of the column-like Right Click on Table name --> Select Design --> Change data length from 200 to 300 and saved the changes.

4 records are inserted in DDLAudit_All table after done the above process statement like below

+--------------+----------+--------------+-----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DatabaseName | ActionOn |    Event     |      ObjectName       |                                                                              TSQL                                                                               |
+--------------+----------+--------------+-----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Pratice_DB   | TABLE    | CREATE_TABLE | Tmp_EmployeeTable_DDL | CREATE TABLE dbo.Tmp_EmployeeTable_DDL   (   Emp_ID int NOT NULL IDENTITY (1, 1),   Emp_name varchar(300) NULL,   Emp_Sal decimal(10, 2) NULL   )  ON [PRIMARY] |
| Pratice_DB   | TABLE    | ALTER_TABLE  | Tmp_EmployeeTable_DDL | ALTER TABLE dbo.Tmp_EmployeeTable_DDL SET (LOCK_ESCALATION = TABLE)                                                                                             |
| Pratice_DB   | TABLE    | DROP_TABLE   | EmployeeTable_DDL     | DROP TABLE dbo.EmployeeTable_DDL                                                                                                                                |
| Pratice_DB   | TABLE    | RENAME       | Tmp_EmployeeTable_DDL | EXECUTE sp_rename N'dbo.Tmp_EmployeeTable_DDL', N'EmployeeTable_DDL', 'OBJECT'                                                                                  |
+--------------+----------+--------------+-----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+

Changing data length of a column using alter statement we can easily find which column data length has changed but changing data length of a column using the design window we are unable to find which column data length has changed.

How to know which column data length has changed when we change the data length using design window.

Maintaining DDL Trigger log for alter table

0 Answers0