Solved

Tracking what tables appear in which queries

Posted on 2007-12-04
6
182 Views
Last Modified: 2010-04-21
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
Comment
Question by:SigmundFraud
  • 2
  • 2
  • 2
6 Comments
 
LVL 7

Accepted Solution

by:
wittyslogan earned 200 total points
Comment Utility
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
 

Author Comment

by:SigmundFraud
Comment Utility
Thanks for the swift response.

They're in some sp's but mainly in views.
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 
LVL 7

Expert Comment

by:wittyslogan
Comment Utility
views should be accesible using the code as well.  we are referencing the sysobjects table
0
 

Author Closing Comment

by:SigmundFraud
Comment Utility
Excellent solution, thanks Witty.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now