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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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