我最近有同样的任务，我使用以下sql server 2012 db。使用management studio并连接到要搜索的数据库。然后执行以下脚本。

Select 
[tgr].[name] as [trigger name], 
[tbl].[name] as [table name]

from sysobjects tgr 

join sysobjects tbl
on tgr.parent_obj = tbl.id

WHERE tgr.xtype = 'TR'

上面的代码是错误的，如下所示:

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' 
EXCEPT
SELECT OBJECT_NAME(parent_id) as Table_Name FROM sys.triggers

不需要与其他表连接…所有信息都可以从sys.objects中获取。

SELECT  name as trigger_name
, object_name(parent_obj) as tableName
, object_schema_name(parent_obj) as schemaName 
,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 s
WHERE s.type = 'TR' 

    CREATE TABLE [dbo].[VERSIONS](
        [ID] [uniqueidentifier] NOT NULL,
        [DATE] [varchar](100) NULL,
        [SERVER] [varchar](100) NULL,
        [DATABASE] [varchar](100) NULL,
        [USER] [varchar](100) NULL,
        [OBJECT] [varchar](100) NULL,
        [ACTION] [varchar](100) NULL,
        [CODE] [varchar](max) NULL,
     CONSTRAINT [PK_VERSIONS] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[VERSIONS] ADD  CONSTRAINT [DF_VERSIONS_ID]  DEFAULT (newid()) FOR [ID]
    GO


    DROP TRIGGER [DB_VERSIONS_TRIGGER] ON ALL SERVER

    CREATE TRIGGER [DB_VERSIONS_TRIGGER] ON ALL SERVER FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, 
    CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION, CREATE_VIEW, ALTER_VIEW, 
    DROP_VIEW, CREATE_TABLE, ALTER_TABLE, DROP_TABLE 
    AS 
    SET NOCOUNT ON SET XACT_ABORT OFF; 
    BEGIN 
        TRY 
            DECLARE @DATA XML = EVENTDATA() 
            DECLARE @SERVER VARCHAR(100) = @DATA.value('(EVENT_INSTANCE/ServerName)[1]','VARCHAR(100)') 
            DECLARE @DATABASE VARCHAR(100) = @DATA.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(100)') 
            DECLARE @USER VARCHAR(100) = @DATA.value('(/EVENT_INSTANCE/LoginName)[1]','VARCHAR(100)') 
            DECLARE @OBJECT VARCHAR(100) = @DATA.value('(EVENT_INSTANCE/ObjectName)[1]','VARCHAR(100)') 
            DECLARE @ACTION VARCHAR(100) = @DATA.value('(/EVENT_INSTANCE/EventType)[1]','VARCHAR(100)') 
            DECLARE @CODE VARCHAR(MAX) = @DATA.value('(/EVENT_INSTANCE//TSQLCommand)[1]','VARCHAR(MAX)' ) 

            IF OBJECT_ID('DB_VERSIONS.dbo.VERSIONS') IS NOT NULL 
            BEGIN 
                INSERT INTO [DB_VERSIONS].[dbo].[VERSIONS]([SERVER], [DATABASE], [USER], [OBJECT], [ACTION], [DATE], [CODE]) VALUES (@SERVER, @DATABASE, @USER, @OBJECT, @ACTION, getdate(), ISNULL(@CODE, 'NA')) 
            END 
        END 
        TRY 
        BEGIN 
            CATCH 
        END 
    CATCH 
    RETURN

我最近有同样的任务，我使用以下sql server 2012 db。使用management studio并连接到要搜索的数据库。然后执行以下脚本。

Select 
[tgr].[name] as [trigger name], 
[tbl].[name] as [table name]

from sysobjects tgr 

join sysobjects tbl
on tgr.parent_obj = tbl.id

WHERE tgr.xtype = 'TR'

给你。

    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.Id
WHERE [so].[type] = 'TR'

这里有几件事…

我也看到你试图拉父表的模式信息，我相信为了这样做，你还需要加入sysobjects表本身，这样你就可以正确地获得父表的模式信息。上面的查询可以做到这一点。此外，结果中不需要sysusers表，因此Join已被删除。

使用SQL 2000, SQL 2005和SQL 2008 R2进行测试