確定 DDL 觸發器中的命令類型/獲取新列的名稱和數據類型
給定對應於 DDL 命令的字元串，我如何確定它究竟是哪個 DDL 命令（列添加、約束添加等）？
我在數據庫上有一個 DDL 觸發器，只要數據庫中的任何表發生更改，就會觸發該觸發器；系統表將記錄已在數據庫中的任何表上觸發的 DDL 命令。
所以命令看起來像：
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);以上4個DDL語句有什麼辦法辨別嗎？我只對向表中添加新列的 DDL 感興趣，並且希望只檢索這個新列的名稱和數據類型。
到目前為止，我一直在煩惱 , 等功能，
SUBSTRING()但運氣不佳。LEN()``CHARINDEX()附言。我的觸發器能夠獲取正在執行 DDL 的表的名稱。
觸發器將簡單地將上述 4 個選項之一作為輸入，並且需要區分何時發生列更改，並且僅檢索新列名稱及其相應的數據類型。
在對錶進行更改後，觸發器將被觸發，這個確切的更改命令將作為我用來跟踪更改的 CDC 功能的一部分記錄在系統表之一中（鎖定我對命令的訪問權限）。此 CDC 系統表還使我能夠檢索已對其進行更改的表的名稱，因此我有另一個關鍵字，即我從 ALTER 命令中知道的表名。
可用於 DDL 觸發器的事件列表可在以下 MSDN 頁面上找到：DDL 事件組。如果您查看該列表，您會注意到它們沒有提供低於基本 CREATE / ALTER / DROP {ObjectType} 的粒度級別……因此擷取
ALTER_TABLE將獲取所有ALTER TABLE...語句。觸發 DDL 觸發器後，您需要使用EVENTDATA()函式以 XML 形式獲取觸發 DDL 觸發器的事件的詳細資訊。不同類型的命令可以有不同的事件相關數據點，所以我們首先需要看看有哪些可用的
ALTER_TABLE。根據 的連結文件EVENTDATA()，可以通過查看以下文件找到各種選項：C:\Program Files\Microsoft SQL Server{version_number}\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd
或者，我們可以通過創建一個 DDL 觸發器來做一個簡單的測試，讓它只返回
EVENTDATA()值讓我們查看它是如何填充的：CREATE TRIGGER [CaptureAlterTableAddColumn] ON DATABASE FOR ALTER_TABLE AS SET NOCOUNT ON; SELECT EVENTDATA() AS [AlterTableEventData];一旦 DDL 觸發器就位，執行以下命令以查看我們必須使用的內容：
CREATE TABLE dbo.AlterTableTest (Col1 INT); BEGIN TRAN ALTER TABLE dbo.AlterTableTest ADD Col2 INT; ROLLBACK;那應該返回以下內容：
<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>所以這看起來很有希望。< **AlterTableActionList>元素具有子元素<Create><Columns>**所以現在我們有一種方法，不需要用
SUBSTRING等進行不精確的文本解析來確定 thisALTER TABLE ADD [column]...與ALTER TABLE ADD CONSTRAINT.... 但是呢ALTER TABLE ALTER COLUMN...？如果你試試：BEGIN TRAN ALTER TABLE dbo.AlterTableTest ALTER COLUMN [Col1] BIGINT; ROLLBACK;它表明**<Create>元素現在是<Alter>。偉大的。但是為什麼<Columns>有一個帶有<Column>**子元素的子元素，而不僅僅是：
<Create> <Column Name="Col2" /> </Create>如果你試試：
BEGIN TRAN ALTER TABLE dbo.AlterTableTest ADD Col3 INT, Col4 DATETIME; ROLLBACK;它將顯示：
... <Create> <Columns> <Name>Col3</Name> <Name>Col4</Name> </Columns> </Create> ...此時我們有以下內容：
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)');現在您只需要
@Query使用 @Columns 中的值作為開始查找數據類型的指南進行解析。考慮到如何建構 T-SQL 的可能變化，它將需要正則表達式來正確提取數據類型。你可能已經有一些 SQLCLR 函式，或者你可以獲得免費版本的SQL#（我是它的作者），它有幾個 RegEx 函式。更新 (2016-07-08)
我最初認為
EVENTDATA()需要解析查詢以獲取數據類型，但@ErikE有一個很好的建議來檢查已添加的實際列。由於列名自然需要是唯一的，並且我們已經有了列名，因此可以輕鬆地加入系統目錄視圖sys.columns **，然後加入其他幾個以獲取其餘資訊。最終結果是：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;一個相當完整的測試是：
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;**我使用的是
sys.columns系統目錄視圖（和其他幾個），而不是INFORMATION_SCHEMA.ROUTINE_COLUMNS因為INFORMATION_SCHEMA不包含 Microsoft SQL Server 特定的屬性。例如，SELECT觸發器中的語句引用了以下不存在的列（以及其他列）INFORMATION_SCHEMA.ROUTINE_COLUMNS：
- is_rowguidcol
- is_identity
- is_computed
- is_sparse
- is_xml_document
- xml_collection_id
此列表取自 SQL Server 2012（列與 SQL Server 2014 相同）。如果您在 SQL Server 2016 上執行，那麼您將有可能需要合併到此觸發器正在執行的任何操作中的其他列。如果您在 SQL Server 2005、2008 或 2008 R2 上執行，那麼您將擁有更少的列（例如
is_sparse在 SQL Server 2008 中添加的）。