Home-grown Data Mining in SQL Server

s a database programmer, when you don't know where the tables and columns you need to do your job are, you're dead in the water. You can browse through Enterprise Manager looking for tables whose columns are likely candidates, but columns often are tersely named in a less-than-intuitive manner, making the search for columns (either join columns for foreign keys or code columns for descriptive data) difficult and error prone.

The flipside of this scenario is an even more enigmatic predicament. You may know which values are required for an application or a report, but not know from where they originate. Schema information can provide some clues. You can acquire it either through the information schema views or—for the diehards among us—from joining sysobjects to whatever it is you are trying to find. However, if you're searching for data, not structural information, you won't find much help out there.

This 10-Minute Solution demonstrates a solution that uses information schema views to guide you through a database with which you are unfamiliar. You can specify the data values you need, and this solution will extract the table and column names that house those values.

How do I find data values in an unfamiliar database when I don't know their location?



Use information schema views to navigate through the database and extract the table and column names that house your values.

Next Page