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
EXEC [email protected]='YourTableHere'
where YourTableHere is the name of your table. You can even include wildcards in the