Tracking what tables appear in which queries

Hi

I have tables A,B,C that need to be renamed. However they are referenced in several queries and i would need some code that would list what queries these tables appear in.

Many thanks!
SigmundFraudAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
wittysloganConnect With a Mentor Commented:
USE <database name>;

select so.name
from sysobjects as so
 inner join syscomments as sc
on so.id = sc.id
where sc.text like '%tablename%'

I assume by queries you mean stored procedures?
0
 
SigmundFraudAuthor Commented:
Thanks for the swift response.

They're in some sp's but mainly in views.
0
 
imitchieCommented:
Tables can be referenced directly by program code. It's hard to know what queries can be run by the various programs that connect to your database. However, if you only refer to Stored Procedures, Functions, and Views, then

select distinct object_name(id) from syscomments where text like '%tbl%'
order by 1
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
imitchieCommented:
It's possible that procedures are created encrypted, so this technique will not work. In that case, you will need to find a decrypter (sql2000 ones are easily found) and actually decrypt each and every proc and drop/create them unencrypted before  syscomments  will reveal anything useful.
0
 
wittysloganCommented:
views should be accesible using the code as well.  we are referencing the sysobjects table
0
 
SigmundFraudAuthor Commented:
Excellent solution, thanks Witty.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.