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