Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.