About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Friday, June 1, 2012

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)









No comments:

Post a Comment