Finding out all the User Tables being used by an application

[NickName]

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.

[Erland Sommarskog]

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

[NickName]

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

[Erland Sommarskog]

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