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.

---

 
DECLARE	 @ColumnName	VARCHAR(255) = 'SomeID'
	,@KeyValue	INTEGER = 99999
	,@Label		VARCHAR(128) = 'Label For @KeyValue'

DECLARE	@Tables as TABLE
	(TableName		VARCHAR(255)
	)

DECLARE	@TableColumnUsage as TABLE
	(TableName		VARCHAR(255)
	,ColumnName		VARCHAR(255)
	,KeyValue		INTEGER
	,KeyCount		INTEGER
	,Label			VARCHAR(128)
	)

DECLARE	@TableName	VARCHAR(255)

DECLARE	@SQLCmd		VARCHAR(MAX)
DECLARE	@SQL		VARCHAR(MAX)

--	--------------------------------------------
INSERT INTO @Tables
SELECT	st.Name

FROM	sys.Columns		as sc
JOIN	sys.Tables		as st
  ON	sc.object_id = st.object_id

WHERE	sc.name = @ColumnName
  AND	st.schema_id = 1	--	dbo

--	--------------------------------------------

SET	@SQLCmd = '
                  SELECT   ''@TableName''
		          ,''@ColumnName''
		          ,@KeyValue
		          ,COUNT(*)	KeyCount
		          ,''@Label''
                  FROM	  @TableName
                  WHERE	  @ColumnName = @KeyValue
                  ';
SET	@TableName = NULL

SELECT	TOP 1
	@Tablename = TableName
FROM	@Tables
ORDER BY TableName

WHILE	@Tablename IS NOT NULL
  BEGIN

	SELECT @SQL = (REPLACE(
			REPLACE(
			  REPLACE(
			   REPLACE(@SQLCmd,'@Tablename',@TableName)
					   ,'@ColumnName',@ColumnName)
					    ,'@KeyValue',@KeyValue)
						,'@Label',@Label)
			)

		INSERT INTO @TableColumnUsage
                        (TableName
                        ,ColumnName
                        ,KeyValue
			,KeyCount
                        ,Label
			)	
		EXEC	(@SQL)
								
		DELETE	@Tables
		WHERE	TableName = @TableName
		
		SET	@TableName = NULL

		SELECT	TOP 1
				@Tablename = TableName
		FROM	@Tables
		ORDER BY TableName
	END

	SELECT	*	
	FROM	@TableColumnUsage
	WHERE	KeyCount > 0

 

---

There are tons of little enhancements you could do here cheaply: multiple column names (for when there is variation in the column naming), multiple values, variable schema, etc. I use this frequently when investigating the impact of removing a domain from a relevant system.
Feel free to use this as appropriate – no guarantees are made about the code! Hope you find it useful.