Sql-server – Determine the command type in a DDL trigger / get names and datatypes of new columns

auditddlsql servert-sqltrigger

Given strings corresponding to DDL commands, how can I determine which DDL command (column addition, constraint addition etc.) it precisely is?

I have a DDL trigger on a database that will be fired whenever any table in the database is altered; a system table will be logging the DDL commands that have been fired on any table in the database.

So the commands will look like:

1. ALTER TABLE [dbo.table] ADD [column] VARCHAR(20) NULL;
2. ALTER TABLE [dbo.table] ADD [column] VARCHAR(20) NULL CONSTRAINT [cons] UNIQUE;
3. ALTER TABLE [dbo.table] WITH NOCHECK ADD CONSTRAINT [cons] CHECK ([column] > 1);
4. ALTER TABLE [dbo.table] WITH NOCHECK ADD [column1] VARCHAR(20) NULL,
   CONSTRAINT [cons] CHECK ([column] > 1);

Is there any way to discern among the above 4 DDL statements? I'm only interested in the DDL that adds a new column to a table, and wish to retrieve only the name and datatype of this newly column.

So far I've been fidgeting with functions like SUBSTRING(), LEN(), and CHARINDEX() without much luck.

Ps. My trigger is able to fetch the name of the table on which the DDL is being performed.

The trigger will simply have one of the above 4 options as input and needs to distinguish when a column change has occurred and only retrieve the new column name and its corresponding data type.

Following an alteration made on a table, the trigger will be fired, this exact alteration command is being logged in one of the system table as part of the CDC feature that I'm using to track changes(cinching my access to the command). This CDC system table also enables me to retrieve the name of the table on which the alteration has been made, ergo i have another keyword i.e. table name that I know from the ALTER command.

Best Answer

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.

UPDATE (2016-07-08)

I originally had thought that the query from EVENTDATA() needed to be parsed in order to get the datatypes, but @ErikE had the great suggestion to check the actual columns that had been added. Since the column names naturally need to be unique, and we have the column names already, that makes it easy to JOIN to the system catalog view sys.columns** and then to a couple of others to get the rest of the info. The final result is:

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

  -- SELECT EVENTDATA() AS [AlterTableEventData]; -- uncomment line for debug

  DECLARE @Columns TABLE ([ColumnName] sysname
                                       COLLATE Latin1_General_100_BIN2
                                       NOT NULL,
                          [Datatype] NVARCHAR(50) NULL);
  DECLARE @Temp XML = EVENTDATA(),
          @SchemaName sysname,
          @ObjectName sysname;

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

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

  -- SELECT * FROM @Columns; -- uncomment line for debug

  SET @SchemaName = EVENTDATA().value(N'(/EVENT_INSTANCE/SchemaName/text())[1]',
                                      N'sysname');
  SET @ObjectName = EVENTDATA().value(N'(/EVENT_INSTANCE/ObjectName/text())[1]',
                                      N'sysname');

  SELECT CASE
           WHEN scol.[user_type_id] IN (165, 167, 173,175)
             THEN styp.[name] + N'(' + CASE scol.[max_length]
                                         WHEN -1 THEN N'MAX'
                                         ELSE CONVERT(NVARCHAR(10), scol.[max_length])
                                       END + N')' -- max_length: 1 - 8000, -1 = MAX
           WHEN scol.[user_type_id] IN (231, 239)
             THEN styp.[name] + N'(' + CASE scol.[max_length]
                                         WHEN -1 THEN N'MAX'
                                         ELSE CONVERT(NVARCHAR(10),
                                                      (scol.[max_length] / 2))
                                       END + N')' -- max_length: (2 - 8000)/2, -1 = MAX
           WHEN scol.[user_type_id] IN (41, 42, 43)
             THEN styp.[name] + N'(' + CONVERT(NVARCHAR(10),
                                               scol.[scale]) + N')' -- scale: 1 - 7
           WHEN scol.[user_type_id] IN (106, 108)
             THEN styp.[name] + N'(' + CONVERT(NVARCHAR(10), scol.[precision])
                  + N', ' + CONVERT(NVARCHAR(10), scol.[scale]) + N')' -- prec. & scale
           ELSE styp.[name]
         END AS [DataType],
         scol.collation_name,
         scol.is_nullable,
         scol.is_rowguidcol,
         scol.is_identity,
         scol.is_computed,
         scol.is_sparse,
         scol.is_xml_document,
         scol.xml_collection_id
  FROM   @Columns col
  INNER JOIN sys.columns scol
          ON scol.[name] = col.[ColumnName] COLLATE Latin1_General_100_BIN2
  INNER JOIN sys.objects sobj
          ON sobj.[object_id] = scol.[object_id]
  INNER JOIN sys.schemas sscm
          ON sscm.[schema_id] = sobj.[schema_id]
  INNER JOIN sys.types styp
          ON styp.[user_type_id] = scol.[user_type_id]
  WHERE   sscm.[name] = @SchemaName
  AND     sobj.[name] = @ObjectName;

And a fairly complete test is:

BEGIN TRAN
ALTER TABLE dbo.AlterTableTest
  ADD Col3 INT, Col4 DATETIME, cOl5 NvARchar(13), col6 nvarchar(max),
      col7 datetime2(4), COL8 DEciMAL(18, 9), CoL9 NUMERIC(10),
      col10 sql_variant, col11 float, col12 float(2), col13 float(22),
      col14 varchar(1111), col15 VARchar(mAx), coL16 varbinary(max),
     col17 varbinary(333), col18 binary(334), col19 nchar(789),
      col20 char(77) sparse NULL, col21 xml NOT Null,
      col22 uniqueIdentifier ROWGUIDCOL, col23 AS ([Col3] * 1.2);
ROLLBACK;

** I am using the sys.columns system catalog view (and a couple of others) instead of INFORMATION_SCHEMA.ROUTINE_COLUMNS because INFORMATION_SCHEMA does not contain Microsoft SQL Server-specific properties. For example, the SELECT statement in the Trigger references the following columns (among others) that are not present in INFORMATION_SCHEMA.ROUTINE_COLUMNS:

  • is_rowguidcol
  • is_identity
  • is_computed
  • is_sparse
  • is_xml_document
  • xml_collection_id

This list was taken from SQL Server 2012 (columns are the same for SQL Server 2014). If you are running on SQL Server 2016 then you will have additional columns that might need to be incorporated into whatever this Trigger is doing. And if you are running on SQL Server 2005, 2008, or 2008 R2, then you will have a few less columns (e.g. is_sparse was added in SQL Server 2008).

Related Question