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).
