Parsing mapping files for usage information

Posted by Pramod Sadalage on Thursday, July 19, 2007

Currently working on a legacy application, thats been in production for a long time now. I wanted to find out what are the Tables and Columns being used by the application. Since we could see that some table columns where not being used. We are using a Object Relational mapping framework on the project, so we decided to write some code that would parse all the mapping files and gives us a list of table names and columns. We used this list to create rows in a table with two columns tablename and columnname. Once the table had this data, we just ran one more SELECT against the metadata of the database and our table which pretty much gave us a list of Table and Columns that we are not using

The SQL we used to get the Tables and Columns not used, from Oracles metadata was

SELECT table_name,column_name FROM user_tab_columns
SELECT usedTableName, usedColumnName FROM usedTableColumns

I thought that was a pretty easy way to find out all the tables, columns used by the application and not to have to do manual analysis.