Retrieve Triggers from the SQL Command Prompt


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 [email protected]='YourTableHere'

where YourTableHere is the name of your table. You can even include wildcards in the table's name.
—Bill McEvoy

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.