Get all foreign key relationships in a database where tables are not empty

Question

I'm trying to improve this query.Link

But the system complains about the "tp" (Invalid object name 'tp'.)

Is there a way to get foreign keys from the tables which have rows (are not empty).

SELECT
    fk.name 'FK Name',
    tp.name 'Parent table',
    cp.name, cp.column_id,
    tr.name 'Refrenced table',
    cr.name, cr.column_id
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
    sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
    sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
    where (SELECT COUNT(*) FROM tp)>0 --HERE IS THE PROBLEM WITH tp
ORDER BY

    tp.name, cp.column_id

You cannot use a table alias in a subquery in a `FROM` clause. You need to use the real name and correlate it in the way you want in the `WHERE` clause. — sticky bit, Jul 14 '20 at 13:16

score 0 · Accepted Answer · answered Jul 14 '20 at 13:22

Use an EXISTS correlated subquery to identify non-empty tables. Below is an example that uses sys.partitions.

SELECT
    fk.name 'FK Name',
    tp.name 'Parent table',
    cp.name, cp.column_id,
    tr.name 'Refrenced table',
    cr.name, cr.column_id
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
    sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
    sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
WHERE EXISTS(SELECT 1 FROM sys.partitions as p WHERE tp.object_id = p.object_id AND p.index_id IN(0,1) AND p.rows > 0)
ORDER BY
    tp.name, cp.column_id;

Get all foreign key relationships in a database where tables are not empty

1 Answers1