Loner Ratio - how many tables have no relationships in Azure SQL Database

Rene Castro - Dataedo Team Rene Castro 2018-12-10

Table of Contents:


    This query listed tables that have no foreign keys, meaning they are not referencing any table or are not on the "many" side of the FK.

    The query below lists something a little different - tables that are not referencing and are not referenced by other tables. Something that can be called as Loner Tables. This diagram illustrates the concept:

    Learn more about Loner Tables

    Query

    select count(*) as [table_count],
        sum(case when fks.cnt + refs.cnt = 0 then 1 else 0 end)
        as [loner_tables],
        cast(cast(100.0 * sum(case when fks.cnt + refs.cnt = 0 then 1 else 0 end)
        / count(*) as decimal(36, 1)) as varchar) + '%' as [loner_ratio]
    from (select schema_name(tab.schema_id) + '.' + tab.name as tab,
            count(fk.name) as cnt
        from sys.tables as tab
            left join sys.foreign_keys as fk
                on tab.object_id = fk.parent_object_id
        group by schema_name(tab.schema_id), tab.name) as fks
        inner join
        (select schema_name(tab.schema_id) + '.' + tab.name as tab,
            count(fk.name) as cnt
        from sys.tables as tab
            left join sys.foreign_keys as fk
                on tab.object_id = fk.referenced_object_id
        group by schema_name(tab.schema_id), tab.name) as refs
        on fks.tab = refs.tab
    

    Columns

    • table_count - number of tables in the database
    • loner_tables - number of Loner Tables in the database
    • loner_ratio - Loner Ratio - % of Loner Tables in the database

    Rows

    • Scope of rows: the query returns one row

    Sample results

    Loner Ratio in the AdventureWorksLT database is 33.3%, meaning that 33.3% of tables are not related by foreign keys to any other table.

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept