Найти все триггеры базы данных

#sql #sql_server #триггер

Необходимо найти все триггеры, родителем которых является БД.
Думал выполню скрипт ниже и будет мне счастье, но нет

select *
from [sys].[triggers]
where [parent_class_desc] = 'DATABASE'  


Подскажите пожалуйста, как получить все триггеры которые висят на указанной БД.
    

Ответы

Ответ 1

Ваш запрос вернёт информацию о DDL-триггерах (если они, конечно, есть в БД).

Чтобы найти DML-триггеры фильтруйте по parent_class_desc = 'OBJECT_OR_COLUMN'

select
    schemaName = s.name,
    objectName = o.name,
    triggerName = tr.name
from
    sys.triggers tr
    join sys.objects o on o.object_id = tr.parent_id
    join sys.schemas s on s.schema_id = o.schema_id
where
    tr.parent_class_desc = 'OBJECT_OR_COLUMN'
order by
    s.name, o.name, tr.name


Ответ 2

Системные таблицы для каждой базы данных свои. Поэтому нужно сначала выбрать БД:

use database
select name from sys.triggers;


Ответ 3

Поскольку sys.triggers у каждой базы свой, а вам нужны триггеры со всех баз на сервере,
то как вариант, можно сначала пройтись по всем базам и все записи о триггерах впихать
во временную таблицу. Тут собственно, либо составить через union динамический sql запрос,
либо выполнить запрос с помощью недокументированной sp_MSForEachDB.

Возможно есть пути и проще, но за неимением других ответов:

DECLARE @sql nvarchar(MAX);


IF(OBJECT_ID('tempdb.dbo.#tdata') IS NOT NULL)
    DROP TABLE #tdata;

CREATE TABLE #tdata (dbname varchar(255), trname varchar(255));

SELECT @sql = STUFF(
             (SELECT  char(10) + 'UNION' + char(10)+ ' select ''' + name + ''' as
dbname, name  COLLATE Cyrillic_General_CI_AS as trname from [' + name  + '].sys.triggers' 
              FROM master.sys.databases 
              WHERE state = 0           -- базы, которые онлайн
              FOR XML PATH (''))
             , 1, 6, '')




SET @sql = 'insert into #tdata ' + @sql;
--PRINT @sql;
exec sp_executesql @sql;


SELECT * from #tdata;

DROP TABLE #tdata;


К сожалению, структуру временной таблицы приходится описывать, т.к. select * into
не будет работать из-за ограничений области видимости.

Поскольку базы могут быт созданы с разным COLLATION, то приходится явно указывать
collate при выборке, хотя может обойдетесь и без.

с sp_MSForEachDB проще будет, но я про нее вспомнил когда уже написал остальной запрос.

если же вы заведомо знаете имя БД, то не надо опрашивать все базы, просто изначально
сформировать запрос на выборку из @dbname + '.sys.triggers'