A few months ago I wrote an ad-hoc SQL script to help with the frustrating task of, "Where is this value coming from in the database?" It can be incredibly useful if you are inheriting a database from another team, or are developing on an open-source database application. The script uses SQLServer 2005 or later to cycle through each table and each column* in all tables to see if that column is equal to some string or value we're seeking. It then returns three result sets:
- List of tables that had a column matching the search criteria
- Table/column pairs of every column that satisfied the WHERE clause
- SQL queries you may run in order to retrieve the matching results (one query per table), with the SELECT returning the matching columns before the full set of columns from the table
Continue reading to view and download the SQL script.