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