Checking Table Relations in MS SQL Server

To find isolated tables (that is, tables without foreign key constraints referencing to or from another table) in your data model, use this query:

SELECT  name
FROM    sys.objects t
LEFT OUTER JOIN sys.foreign_key_columns AS FKC1 
    ON t.object_id = FKC1.parent_object_id
LEFT OUTER JOIN sys.foreign_key_columns AS FKC2 
    ON t.object_id = FKC2.referenced_object_id
WHERE (t.type = 'U')
GROUP BY t.name
HAVING (COUNT(FKC1.constraint_object_id) = 0)
AND    (COUNT(FKC2.constraint_object_id) = 0)

If you have a naming convention for foreign key columns, such as FooID or Bar_ID, you can check this requirement:

SELECT Detail.name, DCol1.name AS Column1, Master.name
FROM sys.objects AS Detail
INNER JOIN sys.columns AS DCol1 ON Detail.object_id = DCol1.object_id
INNER JOIN sys.foreign_key_columns AS FKC1
    ON DCol1.column_id = FKC1.parent_column_id
    AND DCol1.object_id = FKC1.parent_object_id
INNER JOIN sys.objects AS Master ON FKC1.referenced_object_id = Master.object_id
WHERE DCol1.name NOT LIKE N'%[_]ID'
AND   DCol1.name NOT LIKE N'%[_]ID[_]%'

To check the opposite, i.e. a column with final “ID” is not part of a foreign key:

SELECT Detail.name, DCol1.name
FROM sys.objects AS Detail
INNER JOIN sys.columns AS DCol1 ON Detail.object_id = DCol1.object_id
LEFT OUTER JOIN sys.foreign_key_columns AS FKC1 
    ON  DCol1.column_id = FKC1.parent_column_id
    AND DCol1.object_id = FKC1.parent_object_id
WHERE (DCol1.name LIKE N'%[_]ID' or DCol1.name LIKE N'%[_]ID[_]%')
AND   (FKC1.constraint_object_id IS NULL)
AND   (Detail.type = 'U')

To check the requirement, that the foreign key of a column with a certain name must always reference a certain table:

SELECT Detail.name, Master.name
FROM sys.objects AS Detail
INNER JOIN sys.columns AS DCol1 ON Detail.object_id = DCol1.object_id
INNER JOIN sys.types on DCol1.system_type_id = sys.types.system_type_id
LEFT OUTER JOIN	sys.foreign_key_columns AS FKC1
    INNER JOIN	sys.objects AS Master 
        ON FKC1.referenced_object_id = Master.object_id
    ON DCol1.column_id = FKC1.parent_column_id
    AND DCol1.object_id = FKC1.parent_object_id
WHERE Detail.schema_id =1
AND   DCol1.name = 'STATUS'
AND   ISNULL(Master.Name, '') <> 'StatusValues'

And finally, to check whether all foreign key relations can make use of an index, use:

SELECT t.name, c.name, r.name, ic.index_column_id
FROM sys.objects t
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.foreign_key_columns fkc 
    ON fkc.parent_object_id = c.object_id
    AND fkc.parent_column_id = c.column_id
INNER JOIN sys.objects fk ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.objects r ON fkc.referenced_object_id = r.object_id
LEFT OUTER JOIN sys.index_columns ic
        INNER JOIN sys.indexes i 
            ON  ic.object_id = i.object_id AND ic.index_id = i.index_id
    ON c.object_id = ic.object_id AND c.column_id = ic.column_id
LEFT OUTER JOIN sys.index_columns ic1
    ON  c.object_id = ic1.object_id 
    AND c.column_id = ic1.column_id 
    AND ic1.index_column_id = 1
WHERE t.schema_id = 1
AND   ISNULL(ic.index_column_id, -1) != 1
AND   ic1.index_column_id IS NULL
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: