find all triggers

How do I query the schema views (preferably) or sys tables to find all
triggers on my db?
tia
chrisDisplays the name of the table and the triggers:
Select c.name,a.name
From sysobjects a, sysdepends b, sysobjects c
Where a.id = b.id
And b.depid = c.id and a.type = 'TR' and c.type = 'U'
'U' defines tables, views are not incuded here, for that, leave out the last
part
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chris" <Chris@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BD6E22CA-A815-4CD1-AD15-590E6C168146@.microsoft.com...
> How do I query the schema views (preferably) or sys tables to find all
> triggers on my db?
> tia
> chris|||Select name TriggerName,
object_name(parent_obj) TableName
from sysobjects
Where type = 'TR'
"Chris" wrote:

> How do I query the schema views (preferably) or sys tables to find all
> triggers on my db?
> tia
> chris|||Hi Chris,
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:BD6E22CA-A815-4CD1-AD15-590E6C168146@.microsoft.com...
> How do I query the schema views (preferably) or sys tables to find all
> triggers on my db?
Try this:
select
trig.name as [Trigger Name],
tab.name as [Table Name],
case
when ObjectProperty( trig.id, 'ExecIsInsteadOfTrigger') = 1 then 'INSTEAD OF
' else 'FOR ' end
+ substring (
case when ObjectProperty( trig.id, 'ExecIsInsertTrigger') = 1 then ',
INSERT' else '' end +
case when ObjectProperty( trig.id, 'ExecIsUpdateTrigger') = 1 then ',
UPDATE' else '' end +
case when ObjectProperty( trig.id, 'ExecIsDeleteTrigger') = 1 then ',
DELETE' else '' end
, 3, 100) as [Trigger for]
from sysobjects as trig
inner join sysobjects as tab on tab.id = trig.parent_obj
where trig.type = 'TR'
order by tab.name
Or this:
select
object_name(parent_obj) as TableName,
name as TriggerName,
case(OBJECTPROPERTY(id, 'ExecIsTriggerDisabled'))
when 0 then 'YES'
when 1 then 'NO' end as Enable,
case(OBJECTPROPERTY(id, 'ExecIsInsertTrigger'))
when 0 then 'NO'
when 1 then 'YES' end as [Insert],
case(OBJECTPROPERTY(id, 'ExecIsUpdateTrigger'))
when 0 then 'NO'
when 1 then 'YES' end as [Update],
case(OBJECTPROPERTY(id, 'ExecIsDeleteTrigger'))
when 0 then 'NO'
when 1 then 'YES' end as [Delete],
case(OBJECTPROPERTY(id, 'ExecIsAfterTrigger'))
when 0 then 'Instead of'
when 1 then 'After' end as [Type],
crdate as CreationDate
from
sysobjects
where
type = 'TR'
order by
object_name(parent_obj), name

> tia
> chris
HTH,
Andrea - www.absistemi.it

Find all references to a database

Is there a way to scan all procedures, functions and triggers in an SQL
server for references to a specific database?
Thanks
Tom G.The following technique isn't perfect but will identify most objects. You
could also script the textual objects to a file and use a find command.
USE MyDatabase
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%SomeDatabase%'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Groszko" <newscorrespondent@.charter.net> wrote in message
news:emfU1rr1DHA.1184@.TK2MSFTNGP10.phx.gbl...
> Is there a way to scan all procedures, functions and triggers in an SQL
> server for references to a specific database?
> Thanks
> Tom G.
>|||The solution I have come up with is:
Create a table like this:
create table PAFCONVERSION (
DBNAME sysname not null,
OBJECTNAME sysname not null,
OBJECTYPE char(2) null,
constraint PK_PAFCONVERSION primary key (DBNAME, OBJECTNAME)
)
Run this in query analyzer, copy the output pane to the execution pane and
run it.
SET NOCOUNT ON
USE master
go
DECLARE DBCURSOR CURSOR FAST_FORWARD FOR
SELECT NAME FROM SYSDATABASES ORDER BY NAME FOR READ ONLY
DECLARE @.DBNAME SYSNAME
OPEN DBCURSOR
FETCH DBCURSOR INTO @.DBNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE @.DBPROCESS varchar(8000)
SET @.DBPROCESS = 'USE ' + @.DBNAME + CHAR(10) + 'GO' + CHAR(10)
+ 'INSERT SDRMONITOR.DBO.PAFCONVERSION (DBNAME, OBJECTNAME, OBJECTYPE)' +
CHAR(10)
+ 'SELECT DISTINCT ''' + @.DBNAME + ''', SYSOBJECTS.NAME, SYSOBJECTS.XTYPE'
+ CHAR(10)
+ 'FROM syscomments' + CHAR(10)
+ 'JOIN SYSOBJECTS ON (SYSCOMMENTS.ID = SYSOBJECTS.ID)' + CHAR(10)
+ 'where text like (''%what you are looking for%'') ' + CHAR(10)
+ 'ORDER BY SYSOBJECTS.NAME, SYSOBJECTS.XTYPE' + CHAR(10)
+ 'GO' + CHAR(10)
SELECT @.DBPROCESS
FETCH DBCURSOR INTO @.DBNAME
END
CLOSE DBCURSOR
DEALLOCATE DBCURSOR
GO
"Tom Groszko" <newscorrespondent@.charter.net> wrote in message
news:emfU1rr1DHA.1184@.TK2MSFTNGP10.phx.gbl...
> Is there a way to scan all procedures, functions and triggers in an SQL
> server for references to a specific database?
> Thanks
> Tom G.
>