Need to list all triggers in SQL Server database with table name and table's schema

Here's one way:

SELECT      sysobjects.name AS trigger_name     ,USER_NAME(sysobjects.uid) AS trigger_owner     ,s.name AS table_schema     ,OBJECT_NAME(parent_obj) AS table_name     ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate     ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete     ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert     ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter     ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof     ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] FROM sysobjects INNER JOIN sysusers     ON sysobjects.uid = sysusers.uid INNER JOIN sys.tables t     ON sysobjects.parent_obj = t.object_id INNER JOIN sys.schemas s     ON t.schema_id = s.schema_id WHERE sysobjects.type = 'TR' 

EDIT:Commented out join to sysusers for query to work on AdventureWorks2008.

SELECT      sysobjects.name AS trigger_name     ,USER_NAME(sysobjects.uid) AS trigger_owner     ,s.name AS table_schema     ,OBJECT_NAME(parent_obj) AS table_name     ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate     ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete     ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert     ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter     ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof     ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] FROM sysobjects /*INNER JOIN sysusers     ON sysobjects.uid = sysusers.uid */  INNER JOIN sys.tables t     ON sysobjects.parent_obj = t.object_id INNER JOIN sys.schemas s     ON t.schema_id = s.schema_id WHERE sysobjects.type = 'TR' 

EDIT 2: For SQL 2000

SELECT      o.name AS trigger_name     ,'x' AS trigger_owner     /*USER_NAME(o.uid)*/     ,s.name AS table_schema     ,OBJECT_NAME(o.parent_obj) AS table_name     ,OBJECTPROPERTY(o.id, 'ExecIsUpdateTrigger') AS isupdate     ,OBJECTPROPERTY(o.id, 'ExecIsDeleteTrigger') AS isdelete     ,OBJECTPROPERTY(o.id, 'ExecIsInsertTrigger') AS isinsert     ,OBJECTPROPERTY(o.id, 'ExecIsAfterTrigger') AS isafter     ,OBJECTPROPERTY(o.id, 'ExecIsInsteadOfTrigger') AS isinsteadof     ,OBJECTPROPERTY(o.id, 'ExecIsTriggerDisabled') AS [disabled] FROM sysobjects AS o /*INNER JOIN sysusers     ON sysobjects.uid = sysusers.uid */  INNER JOIN sysobjects AS o2     ON o.parent_obj = o2.id INNER JOIN sysusers AS s     ON o2.uid = s.uid WHERE o.type = 'TR'

Here you go.

    SELECT    [so].[name] AS [trigger_name],    USER_NAME([so].[uid]) AS [trigger_owner],    USER_NAME([so2].[uid]) AS [table_schema],    OBJECT_NAME([so].[parent_obj]) AS [table_name],    OBJECTPROPERTY( [so].[id], 'ExecIsUpdateTrigger') AS [isupdate],    OBJECTPROPERTY( [so].[id], 'ExecIsDeleteTrigger') AS [isdelete],    OBJECTPROPERTY( [so].[id], 'ExecIsInsertTrigger') AS [isinsert],    OBJECTPROPERTY( [so].[id], 'ExecIsAfterTrigger') AS [isafter],    OBJECTPROPERTY( [so].[id], 'ExecIsInsteadOfTrigger') AS [isinsteadof],    OBJECTPROPERTY([so].[id], 'ExecIsTriggerDisabled') AS [disabled] FROM sysobjects AS [so]INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.IdWHERE [so].[type] = 'TR'

A couple of things here...

Also I see that you were attempting to pull the parent tables schema information, I believe in order to do so you would also need to join the sysobjects table on itself so that you can correctly get the schema information for the parent table. the query above does this. Also the sysusers table wasn't needed in the results so that Join has been removed.

tested with SQL 2000, SQL 2005, and SQL 2008 R2

You can also get the body of triggers as following:

SELECT      o.[name],            c.[text]FROM        sys.objects AS oINNER JOIN  sys.syscomments AS cON      o.object_id = c.idWHERE   o.[type] = 'TR'