Trigger search for table

Question

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi RandyB,

quote:
Is there any known way to search triggers in all tables on a db for references to a specific table?

this is no complete solution but I hope someone else will provide the missing part

SELECT * FROM sysobjects WHERE xtype = 'TR' will give you a list of all triggers in a db

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

RandyB SSCommitted Points: 1565 More actions · Answer 2

Thanks Frank,

You got me thinking....

So I scripted all the tables in the db including triggers and am searching the table name in QA.

Regards,

Randy

shoayb Say Hey Kid Points: 709 More actions · Answer 3

perhaps a better way would be to execute the following code in QA...

select

*

from

syscomments

where

text like '%create trigger%'

and text like '%TABLE_NAME%'

RandyB SSCommitted Points: 1565 More actions · Answer 4

SSCommitted

Points: 1565

August 28, 2003 at 8:46 am

That is excellent...Thanks.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

If your table name might be part of another table name, you could use a more selective LIKE pattern:

select so.name

from sysobjects so

inner join syscomments sc on sc.id = so.id

where so.xtype = 'TR' and sc.text like '%[^A-Z0-9_]TABLE_NAME[^A-Z0-9_]%'

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

If you want the name of the table along with the trigger name:

select tbl.name as TABLE_NAME, trg.name as TRIGGER_NAME

from sysobjects trg

inner join syscomments sc on sc.id = trg.id

inner join sysobjects tbl on tbl.id = trg.parent_obj

where trg.xtype = 'TR' and sc.text like '%[^A-Z0-9_]sales[^A-Z0-9_]%'