none
Tools that shows what columns in a table are never used

    Question

  • I am dealing with a database with lots of useless information that has never been accessed.
    I need a tool that would be able to find out what columns in what tables are never being accessed
    Tuesday, May 14, 2013 10:45 AM

Answers

  • There are couple of things you can do but only if you have access to all application code that’s accessing database.

    - The most precise thing you can do is manual examination but that might take a long time. If your database access code is stored in one set of classes and classes are strongly coupled to database tables then you can just “Find all references” for each database column.
    - Setup profiling for the suspect tables, run it for some time and analyze the logs
    - Setup insert, update, delete triggers on all suspect tables and then store all updated columns in a separate table. This will give you details on what columns are used most often for updates (so the other columns will be the suspects for deleting).
    Tuesday, May 14, 2013 10:48 AM

All replies

  • There are couple of things you can do but only if you have access to all application code that’s accessing database.

    - The most precise thing you can do is manual examination but that might take a long time. If your database access code is stored in one set of classes and classes are strongly coupled to database tables then you can just “Find all references” for each database column.
    - Setup profiling for the suspect tables, run it for some time and analyze the logs
    - Setup insert, update, delete triggers on all suspect tables and then store all updated columns in a separate table. This will give you details on what columns are used most often for updates (so the other columns will be the suspects for deleting).
    Tuesday, May 14, 2013 10:48 AM
  • This is extremely difficult to find out. Say for an example, Select * From table query, it uses all the columns. Hence I would suggest you should be extremely careful if you are taking such decisions on dropping a column...


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, May 14, 2013 11:03 AM