CreateVwTrigger

Here’s a procedure that creates a view that lists all triggers in all tables in all databases on a given instance.

USE Admin

GO

IF object_id('CreateVwTrigger') Is Not Null

      DROP PROC dbo.CreateVwTrigger

GO

CREATE PROC dbo.CreateVwTrigger

      @ShowSql bit = 0

AS

/*    DATE        AUTHOR            REMARKS

      6/1/12            PPaiva            Intial creation.

      DESCRIPTION

            Creates a view in the Admin database for all triggers

                  in all non-system databases on this server.

            System objects are excluded.

            Excluded ReportServer databases due to collation conflicts

                  in the UNION.

            Coded to use Convert(varchar, ServerProperty('ServerName'))

                  rather than @@ServerName since the latter can be erroneous.

      USAGE

            Exec Admin..CreateVwTrigger   1

            SELECT *

            FROM Admin.dbo.vwTrigger

            ORDER BY DB, TableName, TriggerName

            -- DBs that have triggers

            SELECT DB, Count(*) Qty

            FROM Admin.dbo.vwTrigger

            GROUP BY DB

            ORDER BY DB

*/

SET NOCOUNT ON

DECLARE @s varchar(max),

            @DB varchar(100),

            @MaxDB varchar(100),

            @i int,

            @sNow varchar(16)

SET @sNow = Convert(varchar(16), GetDate(), 120)

SET @s = 'IF Object_ID(''dbo.vwTrigger'') Is Not Null

      DROP VIEW dbo.vwTrigger'

IF @ShowSql = 1

      Print @s

Exec(@s)

     

Print ''

SET @s = 'CREATE VIEW dbo.vwTrigger

AS

/*    DATE        AUTHOR            REMARKS

      ' + Convert(varchar(10), GetDate(), 01) + '     PPaiva            Intial creation.

      DESCRIPTION

            Provides a list of triggers for all non-system databases

                  on this server.

      SELECT *

      FROM Admin..vwTrigger

*/

'

-- DROP TABLE #DBs

SELECT  *

INTO #TrigDBs

FROM sys.databases

--SELECT *

--FROM #DBs

--ORDER BY Name

SELECT  @MaxDB = Max(Name),

            @DB = '',

            @i = 0

FROM #TrigDBs

WHILE @DB < @MaxDB

      BEGIN

            SET @i = @i + 1

            SELECT @DB = Min(Name)

            FROM #TrigDBs          

            WHERE Name > @DB

            SET @s = @s + '  

SELECT  Convert(varchar, ServerProperty(''ServerName'')) as Server,

      ''' + @DB + ''' as DB,

      ta.Name COLLATE SQL_Latin1_General_CP1_CI_AS TableName,

      tr.Name COLLATE SQL_Latin1_General_CP1_CI_AS TriggerName,

      ta.crdate TableCreateDate,

      tr.crdate TriggerCreateDate,

''' + @sNow + ''' ViewCreateDate

FROM [' + @DB + '].dbo.sysobjects tr

JOIN [' + @DB + '].dbo.sysobjects ta

ON tr.parent_obj = ta.ID

WHERE tr.xtype = ''tr''

'

                       

            IF @DB <> @MaxDB

                  SET @s = @s + 'UNION ALL '

      END

IF @ShowSql = 1

      BEGIN

            SELECT *

            FROM #TrigDBs

            ORDER BY Name

            Print @s

      END

           

Exec (@s)