how to check if my database triggers and other objects are invoking tables that it's not exist

Question

how to check if my database triggers and other objects are invoking tables that it's not exist

Earl Downs

SSC-Addicted

Points: 437
More actions
May 16, 2011 at 5:49 pm

#239749

Hello, I'm trying to know how to check if my database triggers and other objects are invoking tables that it's not exist.
The problem begin cause I have a database from an ERP software with a lot of tables that we don't need. I'm dropping 200 tables at least, and this work it's really easy. the system have 323 tables after de cleaning.
All the referential integrity work with triggers not forean keys!!!! I know that bad, but that is that we have.:w00t:
The problem is that many triggers refers to tables that were dropped. Instead of a manual check, how can I check my triggers, UDF, SP_ ?
There's a software tool?, some script?:cool:
I thanks in advance!;-)

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

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

The advertising above has been reported. Now, to the OP's question...

Earl Downs (5/16/2011)
Hello, I'm trying to know how to check if my database triggers and other objects are invoking tables that it's not exist.
The problem begin cause I have a database from an ERP software with a lot of tables that we don't need. I'm dropping 200 tables at least, and this work it's really easy. the system have 323 tables after de cleaning.
All the referential integrity work with triggers not forean keys!!!! I know that bad, but that is that we have.:w00t:
The problem is that many triggers refers to tables that were dropped. Instead of a manual check, how can I check my triggers, UDF, SP_ ?
There's a software tool?, some script?:cool:
I thanks in advance!;-)

The easiest way to do this is to use the DMVs and search for those table names in the triggers.

For example (this is the oldschool way but will work in 08)

DECLARE @tablename VARCHAR(500)

SET @tablename = 'MyTableHere'

select

o.name

from

sys.sysobjects as o

join

sys.syscomments as c

ono.id = c.id

where

c.text like '%' + @tablename + '%'

AND o.type = 'tr'

sys.syscomments.text holds the actual declaration of the create trigger statement in it. There's a few new dmvs that let you get there too but I can't remember them off the top of my head...

EDIT: TR, not T, whoops.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

sysobjects and syscomments are deprecated, are included only for backward compatibility, will be removed in a future version of SQL and should not be used any longer.

Use sys.objects and sys.sql_modules instead.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 3

select object_definition(object_id),name from sys.all_objects

where type = 'TR' and object_definition(object_id) like '%your table name%'

you have to create a table of tables list which those you have dropped then create a loop and replace [your table name] with your tables list

sys.triggers also available for this

My Blog
www.aureus-salah.com

Earl Downs SSC-Addicted Points: 437 More actions · Answer 4

Thanks, we really need to thinks in the future.

Regards.

Earl Downs SSC-Addicted Points: 437 More actions · Answer 5

Thanks very much, the script is very practical and functional.

Best Regards

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

What's the point of removing those tables? I've never seen anyone do such a project in those circumstances that anything else than headaches.

Earl Downs SSC-Addicted Points: 437 More actions · Answer 7

Our ERP software was provided for someone that now not exists. And this tables don't matter to us because we don't have the respective application modules. Simply are garbage for our programmers and DBA's.

Regards!