Hi!

   I am not new to SQL but I am new to using T-SQL and the system tables.

   I inherited a SS2K database that is fairly extensive. I need to examine the SQL coding of all triggers in all instances of the datbase. I have Enterprise Manager, but it would be a lengthy process to open all tables of all instances to examine the SQL coding.

   I am able to query system tables but I don't know the schema well enough to do find the information I need.

QUESTION:  can someone point me to a T-SQL query that wil find all triggers in the SS group and list the following?

- Instance name
- Table name
- Trigger name
- SQL code that makes up the trigger

Any help you can provide would be greatly appreciated!

-- Tom

Tom,

The following script should give you what you are needing:

SELECT 
      b.NAME as TableName,
      a.NAME as TriggerName,
      c.TEXT as TriggerText
FROM
      SYSCOMMENTS c
      INNER JOIN SYSOBJECTS a
       ON c.ID = a.ID
       AND c.COLID IS NOT NULL
       AND a.XTYPE = 'TR' -- trigger
 INNER JOIN SYSOBJECTS b
       ON a.parent_obj = b.id
       AND b.XTYPE = 'U' -- user table    
ORDER BY
      b.NAME,
      a.NAME

Please note that you will need to run this using the catalog in question, so the Intance name portion of what you requested should be known when you execute it.

Just a side note: When you get this SS2K database to SS2005/2008 you will want to explore the use of DMVs (Dynamic Management Views) instead of the system tables.

I hope that you find this helpful!

- John