Actually,UPDATE (2016-07-08)
I am not sure how well this parsing is going to work. It would either take 2 passes, or a rather complicated Regular Expressionoriginally had thought that will likely need conditional patternsthe query from (yes, you can do if/then and lots of other super-cool stuff in RegEx patterns). Why? Because what is considered valid T-SQL canEVENTDATA() needed to be far more convoluted than most people would expect (or attemptparsed in order to get the datatypes, thankfully)but @ErikE had the great suggestion to check the actual columns that had been added. StillSince the column names naturally need to be unique, since it is allowedand we have the column names already, that makes it needseasy to be handled. The following are some test cases that all work, but show what is really entailed byJOIN to the requestsystem catalog view sys.columns and then to a couple of others to get "just the datatype" (we already haverest of the column names)info. The final result is:
CREATE
--ALTER
TRIGGER [CaptureAlterTableAddColumn]
ON DATABASE
FOR ALTER_TABLE
AS
SET NOCOUNT ON;
--SELECT EVENTDATA() AS [AlterTableEventData];
DECLARE @Columns TABLE dbo.AlterTableTest([ColumnName] ADDsysname
Col3 INT COLLATE Latin1_General_100_BIN2
NOT NULL;NULL,
ALTER TABLE dbo.AlterTableTest ADD Col2 INT CONSTRAINT [df] DEFAULT [Datatype] NVARCHAR(250) NULL);
ALTER TABLE dbo.AlterTableTestDECLARE ADD@Temp Col2XML AS= 2EVENTDATA(),
* 2; @SchemaName sysname,
ALTER TABLE dbo.AlterTableTest ADD Col3 AS @ObjectName sysname;
INSERT INTO @Columns (2ColumnName)
* 2 SELECT t.c.value(N'(./text();)[1]', N'sysname') AS [ColumnName]
ALTER TABLE dbo FROM @Temp.AlterTableTestnodes(N'/EVENT_INSTANCE/AlterTableActionList/Create/Columns/Name') ADDt(c);
Col3 ASIF (2@@ROWCOUNT *= 20)
PERSISTED; BEGIN
ALTER TABLE dbo.AlterTableTest ADD Col3RETURN; AS-- (2no columns added, so exit
END;
--SELECT * 2)FROM PERSISTED@Columns;
NOT NULLSET PRIMARY@SchemaName KEY;= EVENTDATA().value(N'(/EVENT_INSTANCE/SchemaName/text())[1]', N'sysname');
ALTER TABLE dboSET @ObjectName = EVENTDATA().AlterTableTestvalue(N'(/EVENT_INSTANCE/ObjectName/text())[1]', N'sysname');
SELECT CASE
ADD Col3 AS WHEN scol.[user_type_id] IN (2165, *167, 2173,175)
PERSISTED NOT NULL CONSTRAINT [pk] PRIMARY KEY;
ALTER TABLE dbo.AlterTableTest ADD Col3 AS THEN styp.[name] + N'(2' *+ [Col1]);CASE scol.[max_length]
ALTER TABLE dbo.AlterTableTest ADD Col3 AS (2 /* a comment? */ * /* here*/ [Col1]); WHEN -1 THEN N'MAX'
ALTER TABLE dbo ELSE CONVERT(NVARCHAR(10), scol.AlterTableTest[max_length])
ADD Col3 /* this */ AS /* is */ (2 /* obnoxious, */ * /* yo */ [Col1] END + N');' -- max_length: 1 - 8000, -1 = MAX
WHEN scol.[user_type_id] IN (231, 239)
ALTER TABLE dbo THEN styp.AlterTableTest[name] ADD+ N'(' + CASE scol.[max_length]
Col3 - WHEN -1 pleaseTHEN N'MAX'
AS -- fire ELSE CONVERT(NVARCHAR(10), (scol.[max_length] / 2))
END + N')' -- max_length: (2 - 8000)/2, -1 anyone= MAX
/* who */ *
WHEN scol.[user_type_id] /*IN would(41, */42, 43)
[Col1] THEN styp.[name] + N'(' + CONVERT(NVARCHAR(10), scol.[scale]) + N')' -- actuallyscale: 1 - 7
/* do */ WHEN scol.[user_type_id] IN (106, 108)
THEN styp.[name] + N'(' + CONVERT(NVARCHAR(10), scol.[precision])
+ N', '+ CONVERT(NVARCHAR(10), scol.[scale]) + N')' -- thisprecision, scale:
ELSE styp.[name]
ALTER TABLE dbo END AS [DataType],
scol.AlterTableTestcollation_name,
ADD scol.is_nullable,
Col3 -- you want toscol.is_rowguidcol,
parse on Col3 scol.is_identity,
do ya? scol.is_computed,
AS ( /* well scol.is_sparse,
I have some bad news: scol.is_xml_document,
*/ scol.xml_collection_id
FROM 2 *@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, NOTCol4 NULLDATETIME, cOl5 NvARchar(13), col6 nvarchar(max),
*/ [Col1] col7 datetime2(4), --COL8 thisDEciMAL(18, is9), validCoL9 NUMERIC(10),
Col4 -- yo!col10 sql_variant, col11 float, col12 float(2), col13 float(22),
DATETIME; 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;
