Identifying Trigger Columns

[SQLSquirrel]

In SQLServer 2005, I am building some T-SQL scripts to semi automate a data masking process (as per a ridiculous requirement from my client*).

During one pass of the database I need to exclude any columns which are referenced by a trigger but I am rather perplexed (so far) as to how to identify them. I can find the triggers for any table but haven't established a link to the columns. Does anyone have any SQL which may help?

* The client won't pay for a third party SQLServer Data Masking Tool License so we are building a process to export/import a sub-set of tables which are then all ftp'd to/from a mainframe where they are masked by a licensed tool. These tables are subject to triggers, foreign keys and indices which we have to allow for...

 

[mrdenny]

There's no system table which has the columns which are referenced by the trigger. You'll need to get the trigger source and parse it (probably through a .NET function) to get the columns in question.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[SQLSquirrel]

Thanks for that, I suspected it would come to this!