SQL Server 2008 - How would I create a dynamic query for finding relationships and references to the primary key?

Question

Since I'm new and I've never shared, and yet there are a few useful scripts that I need on a daily basis, I thought I should.

As a new DBA, some data fix DML operations and simple business logical analyses are much faster when I have a template with which I can check the constraints in a very large database with hundreds of tables. Pinal Dave's script (source posted in the answer below) was a great help as I started, but since then I expanded on it a bit to include the possibility to search for referenced ID's in related tables as well.

I hope this proves useful to some of you guys!

Answer 1

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 = 'TABLENAME' -- The name of the table whose constraint connections you wish to see.
SET @SEARCHED_ID = 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 = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = 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 = fc.constraint_object_id
    INNER JOIN sys.objects AS o ON o.OBJECT_ID = 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 = MIN(RowNum), @ROW_MAX = MAX(RowNum) FROM #SEARCHOBJREF

    WHILE @ROW_NOW <= @ROW_MAX
    BEGIN
        SELECT @TNAME = TableName, @COLNAME = ColumnName FROM #SEARCHOBJREF WHERE RowNum = @ROW_NOW

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

        SET @ROW_NOW = @ROW_NOW + 1
    END

    SELECT TableName, ColumnName, MatchCount as 'Matches_for_ID' FROM #SEARCHOBJREF WHERE MatchCount > 0
    DROP TABLE #SEARCHOBJREF

END