how to find triggers it belongs to Database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to find triggers it belongs to Database

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-09-07 : 05:19:08

Hi All,
I have a trigger and i need to find to which database it belongs.

i am usign sp_helptext 'triggername' but it says not exist in current DB

but i can find the trigger by using

select * from sys.triggers

Thanks,
Gangadhar

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-07 : 05:21:21

DDL-Triggers are stored in master as far as I know.

No, you're never too old to Yak'n'Roll if you're too young to die.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-09-07 : 05:56:50

Hi,

Even in master DB also i am not able to find the trigger code
It says DB object not found but i can see in sys.triggers table

Help pls,.,..

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-09-07 : 05:57:59

he object 'val_SRA_AccountPlus_UPDATE_AUDIT' does not exist in database 'master' or is invalid for this operation.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-07 : 06:40:41

One simple way

exec sp_msforeachdb 'if exists(select * from ?.sys.objects where type=''tr'' and name=''val_SRA_AccountPlus_UPDATE_AUDIT'') select ''?'''

As sp_msforeachdb is undocumented, you can simulate it using
http://beyondrelational.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspx

Madhivanan

Failing to plan is Planning to fail

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-09-08 : 03:21:14

Thanks for the Query Madhivanan..!!

I ran this query
exec sp_msforeachdb 'if exists(select * from ?.sys.objects where type=''tr'' and name=''val_SRA_AccountPlus_UPDATE_AUDIT'') select ''?'''

And i got to know this is exist in Personal DB

But still i am not able to view the code for this Trigger

Use Personal
sp_helptext 'val_SRA_AccountPlus_UPDATE_AUDIT'
Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54
The object 'val_SRA_AccountPlus_UPDATE_AUDIT' does not exist in database 'Personal' or is invalid for this operation.

Any help in this regard helpful.

Thanks,
Gangadhar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-08 : 05:12:45

What does this return?

Use Personal
Go
select * from sys.triggers where name='val_SRA_AccountPlus_UPDATE_AUDIT'

Madhivanan

Failing to plan is Planning to fail

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-09-08 : 06:21:08

Its returning the 1 row with following details:
val_SRA_AccountPlus_UPDATE_AUDIT 1865317955 1 OBJECT_OR_COLUMN 943550645 TR SQL_TRIGGER 2005-10-21 10:29:44.520 2005-10-21 10:29:44.520 0 0 0 0

Ancy
Starting Member

23 Posts

Posted - 2010-09-08 : 06:31:56

Try this

select * from syscomments where object_name(id) = 'val_SRA_AccountPlus_UPDATE_AUDIT'

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-09-08 : 06:39:31

Hi Ancy,

Thanks it works out for me..Thanks a lot.,

But i am loosing all the intendmention .

Ancy
Starting Member

23 Posts

Posted - 2010-09-08 : 07:32:18

Change your results pane to text mode using [CTRL]+T.
Then use the following query
select text from syscomments where object_name(id) = 'val_SRA_AccountPlus_UPDATE_AUDIT'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources