Neither Enterprise Manager nor SQL Server Management Studio (SSMS) lets you view
a complete list of triggers for a given database. Instead, you have to expand the triggers
portion of the tree for each table. Because I primarily work inside a query window,
the more work I can do from the SQL command prompt, the better. So, I created the
sp_ListTriggers stored procedure to list all the triggers in the current database. I wrote
sp_ListTriggers, which you can download from the SQL Server Magazine Web site, for
SQL server 2000.
To use sp_ListTriggers to list all the triggers in the current database, you execute the
following command in Query Analyzer:
EXEC sp_ListTriggers
Optionally, you can use sp_ListTriggers to list only those triggers for a specific table. You
simply pass in the name of the table using the command
EXEC sp_ListTriggers@Table='YourTableHere'
where YourTableHere is the name of your table. You can even include wildcards in the
table's name.
—Bill McEvoy