How to validate a trigger in SQL Server

Question

My problem is this: sometimes changes to database schema invalidate the SQL statements in a trigger. For instance I may drop a column in a table that a trigger references. When I do that, I do not receive any indication of that problem until the next time the trigger fires, which might be months out in production in certain situations.

What is the best way to validate objects, such as triggers or user defined functions, in a SQL server database?

Answer 1

Have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

In your case: search for e.g. columns that you've dropped, and let SQL Search find all stored procs, triggers etc. that referenced that column -> gives you an easy list of those things that you need to fix.

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??