« Copying a really long row in SQL | Main | Google Web Accelerator - A Small Hack »
April 14, 2005
Where's My Data?
At my current job, we have an Oracle database with several hundred tables. This database holds all the information for a piece of web-based software with many features and hidden corners.
Typically, when a user fills out a form, one or many tables get updated. However, it can get tricky when you are not sure which tables were affected.
Luckily, Oracle has generated tables available called ALL_TAB_COLUMNS and USER_TAB_COLUMNS, which show every column and table in either all schemas or whatever schema you are currently using. With this table, I can loop through each column in each table and scan for a specific value.
For example, let's say that there is a row in some table that has 'Elm St.' somewhere inside it but I am not sure of the table or column name. All I would need to do is select every column and its associated table from ALL_TAB_COLUMNS and then check each column for that value.
Here is a Java program that will do just that. It has come in very handy for me because in a few seconds I can know each table and column in the database that contains a certain value.
Posted by Chuck at April 14, 2005 04:00 PM
Trackback Pings
TrackBack URL for this entry:
http://www.chuckcaplan.com/blog/mt-tb.cgi/17