The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database mapping tool?

Can anyone recommend a product that would map a SQL Server database?

I'm working with a company that has a complex web-based, database-backed application which has been around for 10 years or so, is based on a previous iteration of the application intended for a different purpose, and was basically designed ad-hoc by people who weren't really trained in relational database design. In short, the database is a mess, with a bunch of tables, fields, and SPs that no one can say for sure if they're in use or not. Every time there's a bug/issue, hours are wasted by developers figuring out exactly what's happening before the issue can even begin to be addressed.

There is a task under consideration to refactor the database, and the application, so that it is under control: build MegaApp 2.0. But before we can really get started with that, I need to get some sense of what's really being used in the database now.

So is anyone aware of a product/project that would automate the process of mapping which fields & stored procedures are referenced by other stored procedures, and hopefully even examine the web file system to see what's in use?

If not, maybe I have my mISV idea....
Will Hoffmann Send private email
Friday, February 02, 2007
yeah, every tool will allow you to browse the database, but hook up monitoring and browse down into all of the activity going on with any database object. Now that would be nice.
Friday, February 02, 2007
Well, much as I would love to spend the next three months browsing the database to figure what's being used where, any recommendations for automated tools that will provide the information in three minutes?
Will Hoffmann Send private email
Friday, February 02, 2007
At a very basic level you must be able to get a picture of the dependencies with no special tools. Oracle has the view (USER|ALL|DBA)_DEPENDENCIES that will let you build a graph of dependencies from object to object. So, if you're about to redefine a table, you can see what views, packages, etc. you'll have to redefine.

I don't know SQL Server at all well, but a glance at the Management Studio help system suggests that sys.sql_dependencies is the place to start.
George Jansen Send private email
Friday, February 02, 2007
I have enjoyed using Red Gate's SQL Dependency Tracker:
Franklin Send private email
Friday, February 02, 2007
I was recently at a launch of the new Visual Studio for Database Professionals product (or whatever the name is!) and someone posed the same question to the panel of experts.

My recollection of their answer was that you could use SQL Profiler to get a rough idea of what objects in the database are used on a consistent basis from various applications. Their caveat to the answer was that you would need to run the trace for extended periods of time in order to get a clear picture of all of the objects used within the db and even that might not be 100% perfect.

As others have suggested, you can probably use the system objects & views to write a query to identify objects and their dependencies.

Also, I have used Visio for Enterprise Architects to reverse engineer a database, though the results have been less than what I had hoped for.

Finally, I know there are third party tools by Quest and Red Gate software which might help you out as well, but I don't have any first hand experience with them so I don't know how well they work.
Friday, February 02, 2007
Thanks, Tim--I'll take a look at Quest and Red Gate.

I've had underwhelming results from Visio myself :-/
Will Hoffmann Send private email
Friday, February 02, 2007
I have used ApexSQL tools in the past and I am very happy with them. 

They have a tool that tracks dependencies between other SQL objects as well as source code.  It's called SqlClean.  This is one of their tools that I have never used.

As far a checking what tables are being used, they have another tool that I think would work.  It's called SQL Audit.  This tool will create triggers to audit Insert, Update, and Deletes against any and all tables in your database.  You could install the auditing and then check back in a few weeks  or so to see which tables have had audit records posted against them.  This would tell you which tables are being used.  Granted it won't tell you anything about tables that only get read from or tables that only get updated occasionally (like once a quarter or  year) but it would be a start.
Chris Hoffman
Friday, February 02, 2007

The link for SQL Audit is ...
Chris Hoffman
Friday, February 02, 2007
I would discourage people from finding which tables are being "used" over time, and instead simply map the references between them. For example, you don't want to profile the database going back six months and then discover there's a critical table that's only updated once a year.

I have similar problems, but I've found that dealing with this at the "business level" is a more reliable indicator. In other words, I create a model of the application and determine what's being called and what it needs. At some point I'll refactor the data layer and get to the point where the code itself neatly and concisely identifies which tables are in use.

The one thing I don't like about my solution is that it's definitely not a short term solution, and for any system of reasonable complexity, it can't be done in less than a month, unlike an automated database mapping tool. If you have the luxury of a long term maintenance plan, I'd definitely recommend this approach though.
Friday, February 02, 2007
Metallect puts out a product called "IQ server" that can do what you need I think.  IMO, a solid product.
Friday, February 02, 2007
You need to run "grep" on the code base looking for anything/everything that touches the database. Run this through some scripts and figure out the dependencies and you'll pretty much know what's being used.

Another way to figure things out: rename the object.

if things start breaking, rename it back.

pragmatist Send private email
Friday, February 02, 2007
+10 for renaming
Fastest way to find if some proc, func or var is ever used.
Saturday, February 03, 2007
Take a look at the sp_depends system sproc. Pass it a db object (table, constraint, sproc, whatever) and it'll tell you what it used by that object. Or vice versa.
Chris Tavares Send private email
Monday, February 05, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz