Return to Revisions

The list of events that can be used for DDL Triggers can be found on the following MSDN page: DDL Event Groups. If you look through that list, you will notice that they do not offer a level of granularity below the base CREATE / ALTER / DROP {ObjectType} ... So trapping ALTER_TABLE will get all ALTER TABLE... statements.

Once a DDL Trigger is fired, you need to use the EVENTDATA() function to get the details, in XML form, of the event that fired the DDL Trigger. Different types of commands can have different data points related to the event, so we first need to see what is available for ALTER_TABLE. According to the linked documentation for EVENTDATA(), the various options can be found by looking at the following file:

C:\Program Files\Microsoft SQL Server\{version_number}\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd

Or, we can do a simple test by creating a DDL Trigger and having it do nothing more than return the EVENTDATA() value for us to see how it gets filled out:

CREATE TRIGGER [CaptureAlterTableAddColumn]
ON DATABASE
FOR ALTER_TABLE
AS
  SET NOCOUNT ON;

  SELECT EVENTDATA() AS [AlterTableEventData];

Once that DDL Trigger is in place, run the following to see what we have to work with:

CREATE TABLE dbo.AlterTableTest (Col1 INT);

BEGIN TRAN
ALTER TABLE dbo.AlterTableTest ADD Col2 INT;
ROLLBACK;

That should return the following:

<EVENT_INSTANCE>
  <EventType>ALTER_TABLE</EventType>
  <PostTime>2015-08-27T14:34:48.730</PostTime>
  <SPID>55</SPID>
  <ServerName>DALI</ServerName>
  <LoginName>Dali\Solomon</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>Test</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>AlterTableTest</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <AlterTableActionList>
    <Create>
      <Columns>
        <Name>Col2</Name>
      </Columns>
    </Create>
  </AlterTableActionList>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
                QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>ALTER TABLE dbo.AlterTableTest ADD Col2 INT;
</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

So this looks promising. The <AlterTableActionList> element has sub-elements <Create>\<Columns> so now we have a way that doesn't require imprecise text parsing with SUBSTRING, etc to determine if this ALTER TABLE ADD [column]... vs ALTER TABLE ADD CONSTRAINT.... But what about ALTER TABLE ALTER COLUMN...? If you try:

BEGIN TRAN
ALTER TABLE dbo.AlterTableTest ALTER COLUMN [Col1] BIGINT;
ROLLBACK;

It shows that the <Create> element is now <Alter>. Great. But why is there a sub-element for <Columns> with a <Column> sub-element instead of it just being:

<Create>
  <Column Name="Col2" />
</Create>

If you try:

BEGIN TRAN
ALTER TABLE dbo.AlterTableTest ADD Col3 INT, Col4 DATETIME;
ROLLBACK;

It will show:

...
<Create>
  <Columns>
    <Name>Col3</Name>
    <Name>Col4</Name>
  </Columns>
</Create>
...

At this point we have the following:

SET NOCOUNT ON;

DECLARE @Columns TABLE ([ColumnName] sysname NOT NULL);

INSERT INTO @Columns (ColumnName)
  SELECT t.c.value(N'(./text())[1]', N'sysname') AS [ColumnName]
  FROM   EVENTDATA.nodes(N'/EVENT_INSTANCE/AlterTableActionList/Create/Columns/Name') t(c);

IF (@@ROWCOUNT = 0)
BEGIN
  RETURN; -- no columns added, so exit
END;

DECLARE @Query NVARCHAR(MAX);
SET @Query = EVENTDATA.value(N'(/EVENT_INSTANCE/TSQLCommand/CommandText/text())[1]',
                             N'NVARCHAR(MAX)');

Now you just need to parse @Query using the values in @Columns as guides for where to start looking for the datatype. Given the possible variations of how T-SQL can be structured, it will require Regular Expressions to properly extract the datatype(s). You might already have some SQLCLR functions for that, or you can get the Free version of SQL# (which I am the author of) which has several RegEx functions.

{I don't have time to put together the proper RegEx expression now, but if I do later I will update with that. But as it currently stands, this is 95% there :-) }