One of the databases I work with has several triggers and one of them is
throwing an error. I thought I knew which table it is on, but that table has
no triggers. Is there an easy way to identify all the triggers in a
database?
Much obliged,
Geoff.select * from sysobjects where xtype ='tr'
http://sqlservercode.blogspot.com/|||Hi
Try
select name from dbo.sysobjects
where Xtype='TR'
lookup OBJECTPROPERTY command which has one opf the many parameters like
'TableHasUpdateTrigger'
"Geoff Pennington" <Geoff.Pennington.ctr@.whs.mil.no_spam> wrote in message
news:OHnL5ajBGHA.1372@.TK2MSFTNGP14.phx.gbl...
> One of the databases I work with has several triggers and one of them is
> throwing an error. I thought I knew which table it is on, but that table
> has no triggers. Is there an easy way to identify all the triggers in a
> database?
> Much obliged,
> Geoff.
>|||Run this query on any db and you will get the listin of all tables that hav
triggers on them
select TBL.name as "Table", TRG.name as "Trigger"
from sysobjects TRG
inner join sysobjects TBL on TRG.parent_obj = TBL.id
where TRG.xtype = 'tr'
order by TBL.name, TRG.name
Robert
"Geoff Pennington" <Geoff.Pennington.ctr@.whs.mil.no_spam> wrote in message
news:OHnL5ajBGHA.1372@.TK2MSFTNGP14.phx.gbl...
> One of the databases I work with has several triggers and one of them is
> throwing an error. I thought I knew which table it is on, but that table
has
> no triggers. Is there an easy way to identify all the triggers in a
> database?
> Much obliged,
> Geoff.
>|||http://www.aspfaq.com/2105
"Geoff Pennington" <Geoff.Pennington.ctr@.whs.mil.no_spam> wrote in message
news:OHnL5ajBGHA.1372@.TK2MSFTNGP14.phx.gbl...
> One of the databases I work with has several triggers and one of them is
> throwing an error. I thought I knew which table it is on, but that table
> has no triggers. Is there an easy way to identify all the triggers in a
> database?
> Much obliged,
> Geoff.
>|||select * from sysobjects where OBJECTPROPERTY(id, N'IsTrigger') = 1
if you then know the name of the trigger you can write
select * from sysobjects where id = object_id(N'[dbo].[TriggerName]') and
OBJECTPROPERTY(id, N'IsTrigger') = 1|||Thanks. Good link.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1135174145.929774.170520@.o13g2000cwo.googlegroups.com...
> select * from sysobjects where xtype ='tr'
>
> http://sqlservercode.blogspot.com/
>|||Thanks.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:OICVJjjBGHA.2356@.tk2msftngp13.phx.gbl...
> Run this query on any db and you will get the listin of all tables that
> hav
> triggers on them
> select TBL.name as "Table", TRG.name as "Trigger"
> from sysobjects TRG
> inner join sysobjects TBL on TRG.parent_obj = TBL.id
> where TRG.xtype = 'tr'
> order by TBL.name, TRG.name
> Robert
>
> "Geoff Pennington" <Geoff.Pennington.ctr@.whs.mil.no_spam> wrote in message
> news:OHnL5ajBGHA.1372@.TK2MSFTNGP14.phx.gbl...
> has
>|||sp_msforeachtable 'exec sp_helptrigger ''?'''
Regards
Amish
No comments:
Post a Comment