Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server database
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 19:15pm
Comments
Sergey Alexandrovich Kryukov at 11-May-13 1:31am
   
This is fully depends on your definition of "use".
—SA
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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.
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 587
1 Sergey Alexandrovich Kryukov 519
2 Maciej Los 305
3 BillWoodruff 250
4 Mathew Soji 195
0 OriginalGriff 7,356
1 Sergey Alexandrovich Kryukov 6,777
2 DamithSL 5,461
3 Manas Bhardwaj 4,916
4 Maciej Los 4,475


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 12 May 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100