SQL Server 2008 - How would I create a dynamic query for finding relationships and references to the primary key?
The query I've used for the scenario above, is below. Let me know what you think, and if you've links to other such generic and useful tips.


/*
Modified from Pinal Dave's original @ SQLAuthority: 
    http://blog.sqlauthority.com/2009/02/26/sql-server-2008-find-relationship-of-foreign-key-and-primary-key-using-t-sql-find-tables-with-foreign-key-constraint-in-database/

Supply the name of the table, and optionally the ID (default = NULL) whose 
references you wish to see from other tables.
*/

DECLARE @SEARCHED_NAME VARCHAR(50), @SEARCHED_ID INT 
SET @SEARCHED_NAME &#061; 'TABLENAME' -- The name of the table whose constraint connections you wish to see.
SET @SEARCHED_ID &#061; NULL -- The parent table ID you want to find in any of the tables referencing the parent table.

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID &#061; fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID &#061; fc.referenced_object_id
WHERE OBJECT_NAME(f.parent_object_id) LIKE @SEARCHED_NAME
OR OBJECT_NAME (f.referenced_object_id) LIKE @SEARCHED_NAME
ORDER BY f.name

IF @SEARCHED_ID IS NOT NULL
BEGIN

    CREATE TABLE #SEARCHOBJREF (RowNum INT IDENTITY(1,1), TableName VARCHAR(256)
        , ColumnName VARCHAR(256), MatchCount INT)

    INSERT INTO #SEARCHOBJREF (TableName, ColumnName)
    SELECT DISTINCT OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID &#061; fc.constraint_object_id
    INNER JOIN sys.objects AS o ON o.OBJECT_ID &#061; fc.referenced_object_id
    WHERE OBJECT_NAME (f.referenced_object_id) LIKE @SEARCHED_NAME
    ORDER BY OBJECT_NAME(f.parent_object_id)

    DECLARE @ROW_NOW INT, @ROW_MAX INT, @SQL VARCHAR(MAX), @TNAME VARCHAR(256), @COLNAME VARCHAR(256)
    SELECT @ROW_NOW &#061; MIN(RowNum), @ROW_MAX &#061; MAX(RowNum) FROM #SEARCHOBJREF

    WHILE @ROW_NOW &#060;&#061; @ROW_MAX
    BEGIN
        SELECT @TNAME &#061; TableName, @COLNAME &#061; ColumnName FROM #SEARCHOBJREF WHERE RowNum &#061; @ROW_NOW

        SET @SQL &#061; 'UPDATE #SEARCHOBJREF '
            + ' SET MatchCount &#061; (SELECT COUNT(*) FROM ' + @TNAME + ' WHERE ' 
            + @COLNAME + ' &#061; ' + CAST(@SEARCHED_ID AS VARCHAR(64)) + ') '
            + 'WHERE RowNum &#061; ' + CAST(@ROW_NOW AS VARCHAR(64))
        EXEC(@SQL)

        SET @ROW_NOW &#061; @ROW_NOW + 1
    END

    SELECT TableName, ColumnName, MatchCount as 'Matches_for_ID' FROM #SEARCHOBJREF WHERE MatchCount &#062; 0
    DROP TABLE #SEARCHOBJREF

END