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.
