Locate all columns that the application don’t use

Question

1.00/5 (2 votes)

See more:

I have interesting task. We are using a bunch of tables filled with data we don’t use anymore. Problem is I don’t have documentation for applications used in the past with our SQL Server tables so I must manually locate unused column data. Is there some tool out there to do this job for me?

Posted 10-May-13 18:15pm

SimonSpa

Add a Solution

Comments

Sergey Alexandrovich Kryukov 11-May-13 1:31am

This is fully depends on your definition of "use".
—SA

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

smagent · Answer 1 · 2013-05-11T19:01:00

I agree it is almost impossible.If you have queries like select * used in your application than you cant just go and delete data ....
First you need to examine your application code. This can be time consuming, but it is a good place to start. Finding all references for each column...
You can do a profiling for tables you want to clean. Just let it run few days and than examine logs.

The only tool i know for this is http://www.red-gate.com/products/sql-development/sql-dependency-tracker/[^]
It can find all "orphan" objects.

Sergey Alexandrovich Kryukov · Answer 2 · 2013-05-10T19:37:00

I think that, given enough time, I could proof that in general case this is theoretically impossible. But I can give you the idea:
http://en.wikipedia.org/wiki/Halting_problem[^].

Are you getting the picture? You may have some "dead code" in your application, which uses some columns but is never actually called. It is theoretically impossible to proof that it is called (or not), which can be proven as a consequence of halting problem. Practically, this is even harder.

In principle, you could do much weaker statements: you could eliminate the columns with names which are not hard-coded in the product. In other words, it can help only some sloppy products and still cannot 100% guarantee that the functionality is not broken. And I would not expect that anyone would be developing a tool like that.

—SA

Locate all columns that the application don’t use

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0