Skip to content

duncand.techxs@gmail.com

Better Queries Through Science : Locating Domain Values.

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.

Read More →

Hi There!

My name is Duncan Davenport, and if you’re here for the first time, allow me to share a little background about myself.

I’m a SQL guy. I wasn’t always a SQL guy, but that’s mainly because things were mostly ISAM when I got started. (If you don’t know what ISAM is, don’t worry about it, almost no one does anymore. ISAM is Indexed Sequential Access Method, and is basically flat files with a secondary index file that points back to rows by key). Anyway, I discovered SQL in the early 90s and never looked back.

Read More →