DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to find all the triggers in a database?

The following Microsoft SQL Server T-SQL stored procedures can be compiled into the master database and use it from any application database. Here are the sprocs:

-- List all triggers(including database level) in the database - QUICK SYNTAX

SELECT *  FROM [AdventureWorks2008].[sys].[triggers]

------------

 

 

-- SQL Server 2005 / SQL Server 2008 list all triggers stored procedure

USE master;

GO

CREATE PROC sp_ListTriggers

AS

  BEGIN

    SELECT   ParentObject = o.name,

             TriggerName = t.name,

             [Type] = t.type,

             TypeDesc = t.type_desc

    FROM     sys.triggers t

      LEFT JOIN sys.objects o

        ON t.parent_id = o.object_id

    WHERE    t.is_ms_shipped = 0

    ORDER BY [Type],

             ParentObject,

             TriggerName

  END

GO

USE AdventureWorks2008;

GO

exec sp_ListTriggers

/*

.....

ParentObject      TriggerName Type  TypeDesc

PurchaseOrderDetail     iPurchaseOrderDetail    TR    SQL_TRIGGER

PurchaseOrderDetail     uPurchaseOrderDetail    TR    SQL_TRIGGER

PurchaseOrderHeader     uPurchaseOrderHeader    TR    SQL_TRIGGER

....

*/
------------

 

-- SQL Server 2000 list all triggers

CREATE PROC sp_ListTriggers2000

AS

  BEGIN

    SELECT   [Trigger] = a.name,

             [Table] = b.name

    FROM     sysobjects a

             INNER JOIN sysobjects b

               ON b.ID = a.parent_obj

    WHERE    a.xtype = 'TR'

             AND b.xtype = 'U'

    ORDER BY b.name

  END

/*

Trigger     Table

.....

iPurchaseOrderDetail    PurchaseOrderDetail

uPurchaseOrderDetail    PurchaseOrderDetail

uPurchaseOrderHeader    PurchaseOrderHeader

.....

*/

Related article:

http://www.sqlusa.com/bestpractices2005/disabletriggerconstraint/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE