• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

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!
0
SigmundFraud
Asked:
SigmundFraud
  • 2
  • 2
  • 2
1 Solution
 
wittysloganCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now