Retrieve Triggers from the SQL Command Prompt

Neither Enterprise Manager nor SQL Server Management Studio (SSMS) lets you viewa complete list of triggers for a given database. Instead, you have to expand the triggersportion 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 thesp_ListTriggers stored procedure to list all the triggers in the current database. I wrotesp_ListTriggers, which you can download from the SQL Server Magazine Web site, forSQL server 2000.

To use sp_ListTriggers to list all the triggers in the current database, you execute thefollowing command in Query Analyzer:

EXEC sp_ListTriggers

Optionally, you can use sp_ListTriggers to list only those triggers for a specific table. Yousimply 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 thetable's name.
—Bill McEvoy