Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
1

This is the case that in the DB I'm checking, there is an archive table which keeps the user history, and there is a trigger or store procedure that after some time delete rows from this table, in order to avoid the oversize of the same, I didn't desing the DB, I'm just taking the mantainance of an application that use this DB, so I don't know the name of these SP's or triggers, what I want to do is locate this SP or trigger, check the code and modify it to leave this "user history" longer on the table.

Someone told me to check the "sysobjects" table, where I can actually see something with the same name of the table, but this is the only information I have been able to retrieve, any advise?

Thank you.

share|improve this question

1 Answer

up vote 3 down vote accepted

Search all code using sys.sql_modules

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules sm
WHERE definition LIKE '%Mytable%'

Or use Red Gate SQL Search which is completely free

Do not use syscomments or INFORMATION_SCHEMA.RUBBISH

share|improve this answer
Can't the fellow also right-click on the target table and hit "Show Dependencies" (or some text to that effect)? – Nick Chammas Nov 16 '11 at 14:27
@Nick Chammas: nope, this is well known as being unreliable – gbn Nov 16 '11 at 14:29
@gbn, I understand that syscomments is for backwards compatability and may be removed from a future release, but can you elaborate on why not to use INFORMATION_SCHEMA? thx – datagod Nov 16 '11 at 14:32
gbn has fully answered my question, thanks a lot. All the best. – farp332 Nov 16 '11 at 15:04
@datagod: same why syscomments as always a poor choice: the definition column is nvarchar(4000). This means you may miss a table reference if in string position >4000 or spans the boundary between nvarchar(4000) rows (depends how you handle it, could concat I suppose if you really wanted... – gbn Nov 16 '11 at 18:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.