470,619 Members | 1,905 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,619 developers. It's quick & easy.

Finding out all the User Tables being used by an application


Here's the situation, developers inherit a web app from someone,
backend SQL db has about 120 user tables and the db is also being used
by other apps. Developers don't have a list of user tables being used
by this app, now, I need to create a new db based on this one, which
would be used by this app only. So, I intend to find all the user
tables being used by this app, then copy its schema and possibly data
as well to a new db. FYI, current ERD is not this app.

One option to find out all the user tables being used by this app is to
do recursive search for FROM and JOIN against the full spectrum of the
uncompiled source code, then, weed through such an extracted list when
in doubt about a particular table, one caveat is a portion of code with
sql stmt could have already been commented (no longer being used though
still in the code).

Better option?

TIA.

Jul 23 '05 #1
3 1987
NickName (da****@rock.com) writes:
Here's the situation, developers inherit a web app from someone,
backend SQL db has about 120 user tables and the db is also being used
by other apps. Developers don't have a list of user tables being used
by this app, now, I need to create a new db based on this one, which
would be used by this app only. So, I intend to find all the user
tables being used by this app, then copy its schema and possibly data
as well to a new db. FYI, current ERD is not this app.

One option to find out all the user tables being used by this app is to
do recursive search for FROM and JOIN against the full spectrum of the
uncompiled source code, then, weed through such an extracted list when
in doubt about a particular table, one caveat is a portion of code with
sql stmt could have already been commented (no longer being used though
still in the code).


If you have the source under version control in SourceSafe and it is
written Visual Basic, C++ or SQL, you can have a look at SSGREP and
SSREPLACE, two tools in my toolset AbaPerls, available from
http://www.abaris.se/abaperls/index.html.

For this particular quest, you would probably best use SSREPLACE to
search for all database tables in the source, and then check how many
hits you got per table. The assumption is that the table names are
distinct enough not to give false positive. The two tools are smart
enough to not search comments, so you don't have to worry about that
part.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
Erland,

Thank you. Your recommended tool sounds perfect for the job,
unfortunately we are not allowed to use outside tool for the job.
Good to know you would use similar approach to tackle the problem in
hand.

Don

Jul 23 '05 #3
NickName (da****@rock.com) writes:
Thank you. Your recommended tool sounds perfect for the job,
unfortunately we are not allowed to use outside tool for the job.


Il mio Dio!

Am I supposed to laugh or cry here?

Not that I think my tools would have been perfect, but they could have
saved you some boring work. And your employer some money. Or any other
tool that could have found. Oh well...
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Devesh Aggarwal | last post: by
2 posts views Thread by Internet Arrow Limited | last post: by
3 posts views Thread by Michael Glaesemann | last post: by
22 posts views Thread by klenwell | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.