How to find foreign key dependencies of a specific row?

If I have a table, TableA:

Id
1
2
3
...

And two other tables:

TableB:

Id, TableAId
1   1
2   1

TableC:

Id, TableAId
1,  1
2,  2

Where TableAId is a FK relationship with TableA.Id.

How do 开发者_开发问答I determine that TableA, Id 1, has three rows pointing to it? And that TableA, Id 2 has one row pointing to it? And more specifically, how do I identify what those rows are? (their table name and Id)

You can use the INFORMATION_SCHEMA views to generate select statements to display the rows in question. I have only tested this against the tables provided in the question, but it could be expanded to work in cases where the keys are multiple columns.

declare @table_schema nvarchar(50) = 'dbo',
        @table_name nvarchar(50) = 'TableA',
        @id int = 1

select fk_col.TABLE_SCHEMA, fk_col.TABLE_NAME, fk_col.COLUMN_NAME, 
    'select * from ' + fk_col.TABLE_SCHEMA + '.' + fk_col.TABLE_NAME + ' t1 '
        + ' inner join ' + @table_schema + '.' + @table_name + ' t2 '
        + ' on t1.' + fk_col.COLUMN_NAME + ' = t2.' + pk_col.COLUMN_NAME
        + ' where t2.' + pk_col.COLUMN_NAME + ' = ' + cast(@id as nvarchar)

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk

    join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pk_col
        on pk.CONSTRAINT_SCHEMA = pk_col.CONSTRAINT_SCHEMA
        and pk.CONSTRAINT_NAME = pk_col.CONSTRAINT_NAME

    join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fk 
        on pk.CONSTRAINT_SCHEMA = fk.UNIQUE_CONSTRAINT_SCHEMA 
        and pk.CONSTRAINT_NAME = fk.UNIQUE_CONSTRAINT_NAME

    join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fk_col
        on fk_col.CONSTRAINT_SCHEMA = fk.CONSTRAINT_SCHEMA
        and fk_col.CONSTRAINT_NAME = fk.CONSTRAINT_NAME

where pk.TABLE_SCHEMA = @table_schema 
    and pk.TABLE_NAME = @table_name
    and pk.CONSTRAINT_TYPE = 'PRIMARY KEY'

The select statements generated:

select * from dbo.TableB t1  inner join dbo.TableA t2  on t1.TableAId = t2.Id where t2.Id = 1
select * from dbo.TableC t1  inner join dbo.TableA t2  on t1.TableAId = t2.Id where t2.Id = 1

and the query results:

Id          TableAId    Id
----------- ----------- -----------
1           1           1
2           1           1

Id          TableAId    Id
----------- ----------- -----------
1           1           1

I don't have SQL on this computer so I can't give You exact code but here is the way you should go. Please, note that I will use SQL Server terminology.

I see no way to do this without dynamic sql, at least in SQL Server.

1. Create temporary table #t with columns FK_TBL_NM, FK_CLMN_VAL.

2. It should not be difficult to get all the fk relationships for pk of TableA:

   SELECT *  FROM sys.foreign_keys 
   WHERE referenced_object_id = OBJECT_ID('TableA') 
   

3. Use a cursor to iterate thru the result of this query and for each generate and execute dynamic sql that will join TableA and table retrieved from cursor and returns FK_TBL_NM (TableB, TableC, ...) and value of fk column.

4. Insert the result into #t (it is tricky to get dynamic sql result into table but do a research on stackoverflow)

Now you have the table that contains one row for each row in TableB, TableC, ...

I know this is feasible because I wrote the code with similar logic for my current project at work just few days ago.

Note that you should probably make your code work with pk/fk with more that one column. Also there are different data types for columns. It complicates things a bit but it is possible.

Every step I listed above is not difficult to implement. However, if you have any difficulties, search on stackoverflow :)

In order to find out what FK relationships exist, you need to inspect the sys catalog views in SQL Server - something like:

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID('TableA')

This will list out all foreign key relationships that exist to TableA.

Once you have that information, it's a pretty simple JOIN between TableA and any of those other tables involved.

Update: once you know that e.g. TableB and TableC reference your TableA, you can find the depdendent rows with a simple JOIN:

SELECT c.*
FROM dbo.TableC c
INNER JOIN dbo.TableA a ON a.ID = c.TableAID  -- or whatever column joins the tables.....
WHERE.......  -- possibly with a WHERE clause...

I couldn't find perfect reusable answer for this so combining a few different answers I created a stored procedure to do the job.

CREATE PROCEDURE GETLINKEDKEYS 
    @PKTABLE_NAME varchar(32) 
    ,@PKTABLE_ID int 
AS
BEGIN 
    DECLARE @fkeys TABLE ( 
        [PKTABLE_QUALIFIER] varchar(32) 
        ,[PKTABLE_OWNER] varchar(3) 
        ,[PKTABLE_NAME] varchar(32) 
        ,[PKCOLUMN_NAME] varchar(32) 
        ,[FKTABLE_QUALIFIER] varchar(32) 
        ,[FKTABLE_OWNER] varchar(3) 
        ,[FKTABLE_NAME] varchar(32) 
        ,[FKCOLUMN_NAME] varchar(32) 
        ,[FKCOLUMN_PKNAME] varchar(32) 
        ,[KEY_SEQ] int 
        ,[UPDATE_RULE] int 
        ,[DELETE_RULE] int 
        ,[FK_NAME] varchar(64) 
        ,[PK_NAME] varchar(32) 
        ,[DEFERRABILITY] int 
    ); 

    INSERT INTO @fkeys 
        ([PKTABLE_QUALIFIER],[PKTABLE_OWNER],[PKTABLE_NAME],[PKCOLUMN_NAME],[FKTABLE_QUALIFIER],[FKTABLE_OWNER],[FKTABLE_NAME],[FKCOLUMN_NAME],[KEY_SEQ],[UPDATE_RULE],[DELETE_RULE],[FK_NAME],[PK_NAME],[DEFERRABILITY]) 
        EXEC sp_fkeys @PKTABLE_NAME; 

    UPDATE FK SET 
        [FKCOLUMN_PKNAME] = (SELECT [COLUMN_NAME] 
            FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] 
            WHERE OBJECTPROPERTY(OBJECT_ID([CONSTRAINT_SCHEMA] + '.' + QUOTENAME([CONSTRAINT_NAME])), 'IsPrimaryKey') = 1 
            AND [TABLE_NAME] = FK.[FKTABLE_NAME]) 
    FROM @fkeys FK 

    DECLARE @StringTable TABLE ([ID] int IDENTITY(1, 1), [Query] varchar(max)); 

    INSERT INTO @StringTable 
    SELECT 
        'SELECT ''' + [PKTABLE_NAME] + ''' AS ''PrimaryTable'' ' + 
        ',''' + [PKCOLUMN_NAME] + ''' AS ''PrimaryTableColumn'' ' + 
        ',PT.[' + [PKCOLUMN_NAME] + '] AS ''PrimaryTableColumnID'' ' + 
        ',''' + [FKTABLE_NAME] + ''' AS ''ForeignTable'' ' + 
        ',''' + [FKCOLUMN_NAME] + ''' AS ''ForeignTableColumn'' ' + 
        ',FT.[' + [PKCOLUMN_NAME] + '] AS ''ForeignTableColumnID'' ' + 
        (CASE WHEN [FKCOLUMN_PKNAME] IS NULL THEN ',NULL' ELSE ',''' + [FKCOLUMN_PKNAME] + '''' END) + ' AS ''ForeignTablePrimaryColumn''' + 
        (CASE WHEN [FKCOLUMN_PKNAME] IS NULL THEN ',NULL' ELSE ',FT.[' + [FKCOLUMN_PKNAME] + ']' END) + ' AS ''ForeignTablePrimaryColumnID''' + 
        'FROM [' + [PKTABLE_NAME] + '] PT ' + 
        'JOIN [' + [FKTABLE_NAME] + '] FT ' + 
        'ON FT.[' + [FKCOLUMN_NAME] + '] = PT.[' + [PKCOLUMN_NAME] + '] ' + 
        'WHERE PT.[' + [PKCOLUMN_NAME] + '] = ' + CONVERT(varchar(16), @PKTABLE_ID) 
    FROM @fkeys; 

    DECLARE @matchTable TABLE ( 
        [PrimaryTable] varchar(32) 
        ,[PrimaryTableColumn] varchar(32) 
        ,[PrimaryTableColumnID] int 
        ,[ForeignTable] varchar(32) 
        ,[ForeignTableColumn] varchar(32) 
        ,[ForeignTableColumnID] int 
        ,[ForeignTablePrimaryColumn] varchar(32) 
        ,[ForeignTablePrimaryColumnID] int 
    ); 
    DECLARE @Count int = (SELECT COUNT([ID]) FROM @StringTable); 
    DECLARE @QueryString varchar(max); 

    WHILE @Count > 0 
    BEGIN 
        SELECT 
            @QueryString = [Query] 
        FROM @StringTable WHERE [ID] = @Count; 

        INSERT INTO @matchTable 
        EXECUTE(@QueryString); 

        SET @Count = @Count - 1; 
    END 

    SELECT 
        * 
    FROM @matchTable; 
END
GO

Example :

EXECUTE [dbo].[GETLINKEDKEYS] 'Users', 32