Get all triggers in an instance

The following code will help you to list all triggers in an instance.

declare @dbs table(databaseId int identity(1,1) not null primary key clustered, DatabaseName nvarchar(100))
create table #TriggersinDbs
(DatabaseName nvarchar(100),
ParentObject nvarchar(300),
TriggerName nvarchar(300)
)
INSERT INTO @dbs(DatabaseName)
SELECT name from master.dbo.sysdatabases where dbid > 4

declare @dbname nvarchar(100), @counter int, @objecttype nvarchar(3)

select @counter = max(databaseId) from @dbs
SET @objecttype = 'TR'
while @counter is not null
begin
select @dbname = DatabaseName from @dbs where databaseId = @counter

exec ( ' use ' + @dbname + '
INSERT INTO #TriggersinDbs(DatabaseName, ParentObject, TriggerName)
select '''+ @dbname + ''' as DatabaseName, object_name(parent_obj) as Parent_object,
name as TriggerName from sysobjects where xtype = '''+ @objecttype +'''')

select @counter = max(databaseId) from @dbs
where databaseId < @counter end SELECT * FROM #TriggersinDbs ORDER BY DatabaseName, ParentObject, TriggerName ASC drop table #TriggersinDbs