How to discover trigger's parent schema?

Question

To discover all triggers in any given MS SQL Server database, I'm currently querying the sysobjects table (which is fine because it works in MS SQL Server 2000 which I have to support) e.g.

SELECT R1.name AS trigger_name, 
       T1.name AS trigger_parent_table_name
  FROM sysobjects AS R1 
       INNER join sysobjects AS T1 
          ON R1.parent_obj = T1.id 
 WHERE R1.xtype = 'tr';

This gives me a reduced list of trigger names and for each I can use

EXEC sp_helptext 'trigger_name_here'

to find the definition. That works fine for databases where only the default dbo schema is used.

I now have a MS SQL Server 2005 database which uses multiple schemas. What is the best way of discovering the schema for each trigger?

Answer 1

You are looking for the parent object for a trigger so it will always be a table. In sys.tables (system view) you get the schema_id and with it you can go sys.schemas (system view too) to get the schema's name.

Hope this helps.

--

EDIT:

The code:

SELECT     sys.objects.name AS [trigger], 
           sys.tables.name AS [table], 
           sys.objects.type, 
           sys.schemas.name AS [schema]
FROM       sys.schemas RIGHT OUTER JOIN
           sys.tables ON sys.schemas.schema_id = sys.tables.schema_id RIGHT OUTER JOIN
           sys.objects ON sys.tables.object_id = sys.objects.parent_object_id
WHERE      sys.objects.type = 'tr'

This is is with sys.tables but you can do it with only sys.objects, this is a general select to look for parents and schemas:

SELECT     O.name, O.type, S.name AS [schema], 
           OP.name AS parent_name, OP.type AS parent_type, SP.name AS parent_schema  
FROM       sys.schemas AS SP RIGHT OUTER JOIN
           sys.objects AS OP ON SP.schema_id = OP.schema_id RIGHT OUTER JOIN
           sys.objects AS O LEFT OUTER JOIN
           sys.schemas AS S ON O.schema_id = S.schema_id ON OP.object_id = O.parent_object_id