tabella - ms sql elenco tabelle



È necessario elencare tutti i trigger nel database di SQL Server con il nome della tabella e lo schema della tabella (10)

Devo elencare tutti i trigger nel database di SQL Server con il nome della tabella e lo schema della tabella.

Sono quasi lì con questo:

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'

Ho solo bisogno di ottenere lo schema della tabella anche.

https://ffff65535.com


E cosa ne pensi di questo: molto breve e pulito :)

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 ;

Ecco qui.

    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 paio di cose qui ...

Inoltre, vedo che stavi tentando di estrarre le informazioni dello schema delle tabelle padre, credo che per farlo dovresti anche unirti alla tabella sysobjects su se stessa in modo da poter ottenere correttamente le informazioni sullo schema per la tabella padre. la query sopra fa questo. Anche la tabella sysusers non era necessaria nei risultati in modo che Join sia stato rimosso.

testato con SQL 2000, SQL 2005 e SQL 2008 R2


Ho avuto lo stesso compito di recente e ho usato quanto segue per sql server 2012 db. Utilizzare lo studio di gestione e connettersi al database che si desidera cercare. Quindi eseguire il seguente 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'

Il codice appena sopra non è corretto come mostrato:

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

Questo è quello che uso (di solito racchiuso in qualcosa che faccio nel modello):

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

Come vedi, è più skosh McGyver, ma penso che ne valga 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

(Scorri verso destra per vedere la colonna finale e più utile)


Se stai cercando ALL trigger, ricorda che MS-SQL ha sia trigger basati su SQL ( sysobjects.type = 'TR' ) che trigger basati su CLR ( sysobjects.type = 'TA' ).


Usa questa query:

    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

questo può aiutare.

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]

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

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' 

questo funziona per me





triggers