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:
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
where YourTableHere is the name of your table. You can even include wildcards in the table's name.