Besoin d'énumérer tous les déclencheurs dans la base de données SQL Server avec le nom de la table et le schéma de la table

voilà.

    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'

deux ou trois choses ici...

aussi je vois que vous essayiez de tirer l'information de schéma des tables parent, je crois que pour le faire vous auriez aussi besoin de rejoindre la table sysobjects sur elle-même afin que vous puissiez correctement obtenir l'information de schéma pour la table parent. la requête ci-dessus ne présente. Aussi la table sysusers n'était pas nécessaire dans les résultats de sorte que la jointure a été retirée.

testé avec SQL 2000, SQL 2005, et SQL 2008 R2

j'ai eu la même tâche récemment et j'ai utilisé ce qui suit pour sql server 2012 db. Utilisez le studio de gestion et connectez-vous à la base de données que vous voulez rechercher. Puis exécutez le script suivant.

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'

vous pouvez aussi obtenir le corps des déclencheurs comme suit:

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'

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

utilisez cette requête :

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

c'est simple et utile.

Et que pensez-vous de ceci: Très court et soigné :)

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 ;

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' 

ce travail pour moi

Utilisez Cette Requête:

    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

cela peut aider.

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]

C'est ce que j'utilise (habituellement enveloppé dans quelque chose que je stuff in Model):

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

comme vous le voyez c'est un skosh plus McGyver, mais je pense que ça en vaut la peine:

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

(Faites défiler à droite pour voir la colonne finale et la plus utile)

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 

si vous cherchez des déclencheurs ALL , souvenez-vous que le MS-SQL A à la fois des déclencheurs basés sur SQL ( sysobjects.type = 'TR' ) et des déclencheurs basés sur CLR ( sysobjects.type = 'TA' ).

le code juste au-dessus est incorrect comme indiqué:

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

L'une des difficultés est que le texte, ou la description, a des fils linéaires. Mon maladroit bidouille, pour l'obtenir en quelque chose de plus tabulaire, est d'ajouter un HTML littéral SELECT de la clause, de copier et de coller le tout dans le bloc-notes, enregistrer avec une extension html, ouvrir dans un navigateur, puis copier et coller dans une feuille de calcul. exemple

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;

vous aurez peut-être encore besoin de batifoler avec des onglets pour obtenir la description dans un champ, mais au moins il sera sur une ligne, que je trouver très utile.