todas - Debe listar todos los desencadenantes en la base de datos de SQL Server con el nombre de la tabla y el esquema de la tabla

obtener esquema de base de datos sql server

Question

Necesito enumerar todos los disparadores en la base de datos de SQL Server con el nombre de la tabla y el esquema de la tabla.

Ya casi llego con esto:

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

Solo necesito obtener el esquema de la tabla también.

Answer 1

¿Y qué piensas de esto: muy corto y limpio :)

SELECT OBJECT_NAME(parent_id) Table_or_ViewNM,
      name TriggerNM,
      is_instead_of_trigger,
      is_disabled
FROM sys.triggers
WHERE parent_class_desc = ''OBJECT_OR_COLUMN''
ORDER BY OBJECT_NAME(parent_id),
Name ;

Answer 2

Aquí hay una forma:

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'' 

EDITAR : Comentó unirse a los usuarios del sistema para que la consulta funcione en 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 : para 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''

Answer 3

Aqui tienes.

    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''

Un par de cosas aqui...

También veo que estaba intentando extraer la información del esquema de las tablas primarias, creo que para hacerlo también tendría que unir la tabla sysobjects en sí misma para que pueda obtener correctamente la información del esquema para la tabla primaria. la consulta anterior hace esto. Además, la tabla sysusers no era necesaria en los resultados, por lo que se ha eliminado la unión.

Probado con SQL 2000, SQL 2005 y SQL 2008 R2.

Answer 4

El código anterior es incorrecto como se muestra:

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

Answer 5

Esto es lo que uso (normalmente envuelto en algo que relleno en el Modelo):

Select
  [Parent] = Left((Case When Tr.Parent_Class = 0 Then ''(Database)'' Else Object_Name(Tr.Parent_ID) End), 32),
  [Schema] = Left(Coalesce(Object_Schema_Name(Tr.Object_ID), ''(None)''), 16),
  [Trigger name] = Left(Tr.Name, 32), 
  [Type] = Left(Tr.Type_Desc, 3), -- SQL or CLR
  [MS?] = (Case When Tr.Is_MS_Shipped = 1 Then ''X'' Else '' '' End),
  [On?] = (Case When Tr.Is_Disabled = 0 Then ''X'' Else '' '' End),
  [Repl?] = (Case When Tr.Is_Not_For_Replication = 0 Then ''X'' Else '' '' End),
  [Event] = Left((Case When Tr.Parent_Class = 0 
                       Then (Select Top 1 Left(Te.Event_Group_Type_Desc, 40)
                             From Sys.Trigger_Events As Te
                             Where Te.Object_ID = Tr.Object_ID)
                       Else ((Case When Tr.Is_Instead_Of_Trigger = 1 Then ''Instead Of '' Else ''After '' End)) +
                             SubString(Cast((Select [text()] = '', '' + Left(Te.Type_Desc, 1) + Lower(SubString(Te.Type_Desc, 2, 32)) +
                                                    (Case When Te.Is_First = 1 Then '' (First)'' When Te.Is_Last = 1 Then '' (Last)'' Else '''' End)
                                             From Sys.Trigger_Events As Te
                                             Where Te.Object_ID = Tr.Object_ID
                                             Order By Te.[Type]
                                             For Xml Path ('''')) As Character Varying), 3, 60) End), 60)
  -- If you like: 
  -- , [Get text with] = ''Select Object_Definition('' + Cast(Tr.Object_ID As Character Varying) + '')''
From 
  Sys.Triggers As Tr
Order By
  Tr.Parent_Class, -- database triggers first
  Parent -- alphabetically by parent

Como ves es un skosh más de McGyver, pero creo que vale la pena:

Parent                           Schema           Trigger name                     Type MS?  On?  Repl? Event
-------------------------------- ---------------- -------------------------------- ---- ---- ---- ----- -----------------------------------------
(Database)                       (None)           ddlDatabaseTriggerLog            SQL            X     DDL_DATABASE_LEVEL_EVENTS
Employee                         HumanResources   dEmployee                        SQL       X          Instead Of Delete
Person                           Person           iuPerson                         SQL       X          After Insert, Update
PurchaseOrderDetail              Purchasing       iPurchaseOrderDetail             SQL       X    X     After Insert
PurchaseOrderDetail              Purchasing       uPurchaseOrderDetail             SQL       X    X     After Update
PurchaseOrderHeader              Purchasing       uPurchaseOrderHeader             SQL       X    X     After Update
SalesOrderDetail                 Sales            iduSalesOrderDetail              SQL       X    X     After Insert, Update, Delete
SalesOrderHeader                 Sales            uSalesOrderHeader                SQL       X          After Update (First)
Vendor                           Purchasing       dVendor                          SQL       X          Instead Of Delete
WorkOrder                        Production       iWorkOrder                       SQL       X    X     After Insert
WorkOrder                        Production       uWorkOrder                       SQL       X    X     After Update

(Desplácese hacia la derecha para ver la columna final y más útil)

Answer 6

Si está buscando ALL desencadenantes, recuerde que MS-SQL tiene desencadenantes basados ​​en SQL ( sysobjects.type = ''TR'' ) y desencadenantes basados ​​en CLR ( sysobjects.type = ''TA'' ).

Answer 7

También puede obtener el cuerpo de desencadenantes como sigue:

SELECT      o.[name],
            c.[text]
FROM        sys.objects AS o
INNER JOIN  sys.syscomments AS c
ON      o.object_id = c.id
WHERE   o.[type] = ''TR''

Answer 8

Tuve la misma tarea recientemente y utilicé lo siguiente para SQL Server 2012 db. Use Management Studio y conéctese a la base de datos que desea buscar. A continuación, ejecute el siguiente script.

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''

Answer 9

Una dificultad es que el texto o la descripción tiene líneas de alimentación. Mi torpe kludge, para obtener algo más tabular, es agregar un literal HTML a la cláusula SELECT , copiar y pegar todo en el bloc de notas, guardar con una extensión html, abrir en un navegador, luego copiar y pegar en una hoja de cálculo. ejemplo

SELECT obj.NAME AS TBL,trg.name,sm.definition,''<br>''
FROM SYS.OBJECTS obj
LEFT JOIN (SELECT trg1.object_id,trg1.parent_object_id,trg1.name FROM sys.objects trg1 WHERE trg1.type=''tr'' AND trg1.name like ''update%'') trg
 ON obj.object_id=trg.parent_object_id
LEFT JOIN (SELECT sm1.object_id,sm1.definition FROM sys.sql_modules sm1 where sm1.definition like ''%suser_sname()%'') sm ON trg.object_id=sm.object_id
WHERE obj.type=''u''
ORDER BY obj.name;

Es posible que aún deba jugar con las pestañas para obtener la descripción en un solo campo, pero al menos estará en una línea, lo que me parece muy útil.

Answer 10

Utilice esta consulta:

    SELECT     
        DB_NAME() AS DataBaseName,  
        S.Name AS SchemaName,               
        T.name AS TableName,
        dbo.SysObjects.Name AS TriggerName,
        dbo.sysComments.Text AS SqlContent,
    FROM dbo.SysObjects 
    INNER JOIN dbo.sysComments ON dbo.SysObjects.ID = dbo.sysComments.ID
    INNER JOIN sys.tables AS T ON sysobjects.parent_obj = t.object_id 
    INNER JOIN sys.schemas AS S ON t.schema_id = s.schema_id 
    WHERE dbo.SysObjects.xType = ''TR'' 
        AND dbo.SysObjects.Name LIKE ''Permit_AfterInsert'' ---- <----- HERE

Answer 11

esto puede ayudar

SELECT DISTINCT o.[name] AS [Table]
FROM    [sysobjects] o
JOIN    [sysobjects] tr
    ON  o.[id] = tr.[parent_obj]
WHERE   tr.[type] = ''tr''
ORDER BY [Table]

Get a list of tables and all their triggers.

SELECT DISTINCT o.[name] AS [Table], tr.[name] AS [Trigger]
FROM    [sysobjects] o
JOIN    [sysobjects] tr
    ON  o.[id] = tr.[parent_obj]
WHERE   tr.[type] = ''tr''
ORDER BY [Table], [Trigger]

Answer 12

Utilice esta consulta:

SELECT OBJECT_NAME(parent_id) as Table_Name, * FROM [Database_Name].sys.triggers

Es simple y útil.

Answer 13

    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

Answer 14

SELECT
   ServerName   = @@servername,
   DatabaseName = db_name(),
   SchemaName   = isnull( s.name, '''' ),
   TableName    = isnull( o.name, ''DDL Trigger'' ),
   TriggerName  = t.name, 
   Defininion   = object_definition( t.object_id )

FROM sys.triggers t
   LEFT JOIN sys.all_objects o
      ON t.parent_id = o.object_id
   LEFT JOIN sys.schemas s
      ON s.schema_id = o.schema_id
ORDER BY 
   SchemaName,
   TableName,
   TriggerName

Answer 15

SELECT 
     sysobjects.name AS trigger_name   ,OBJECT_NAME(parent_obj) AS table_name ,s.name AS table_schema 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 


    ,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'' 

esto funciona para mi

Answer 16

SELECT tbl.name as Table_Name,trig.name as Trigger_Name,trig.is_disabled  
FROM [sys].[triggers] as trig inner join sys.tables as tbl on 
trig.parent_id = tbl.object_id