Sometimes you need to find all the places where a particular domain value is being used. Domain tables (called Dimension tables in Data Warehousing projects) are those (usually) small tables that associate an integer or code key with some description and perhaps a small set of consistent data. Think your product table, or the table that holds all your salespeople.
It is not uncommon to find domain values used in tables without enforced referential integrity. This is not always a bad practice: particularly in systems where the data is transferred around in a manner that does not assure that a domain table is up-to-date before some data table is made active. In any case, a lot of large systems have very loose RI.
So if you are reassigning all of one salesperson’s data to a new salesperson (or any equivalent scenario) you need to know where that domain key shows up in any and all tables within your database. The following query script takes the name of the domain key, finds all the tables in the current database that contain that column name, and then queries those tables for a specific value.
Better Queries Through Science : Locating Domain Values.
Read More →