Just Code‎ > ‎

TSQL - Hierarchical table dependency graph based on foreign keys

posted Jun 23, 2011, 5:47 AM by Peter Henell   [ updated Jan 25, 2012, 6:45 AM ]
How to find out what table have some sort of relationship to another table? Recursively?

This snippet will output Tables that reference no tables (Level zero(0)), then those that reference level 0 (This is called level one(1)) and so on...
Based on a comment in this post.

Updated 2012-01-25: Can now handle tables that references themselves.

WITH base AS (

    
select 
    tbl.name as [Table_Name]
    ,SCHEMA_NAME(tbl.schema_id) AS Table_Schema
    ,tbl2.name AS [ReferencedTable]
from 
    sys.tables as tbl
left join 
    sys.foreign_keys as cstr
    on cstr.parent_object_id=tbl.object_id
left join 
    sys.foreign_key_columns as fk
    on fk.constraint_object_id=cstr.object_id
left join 
    sys.columns as cfk
    on fk.parent_column_id = cfk.column_id
    and fk.parent_object_id = cfk.object_id
left join 
    sys.columns as crk
    on fk.referenced_column_id = crk.column_id
    and fk.referenced_object_id = crk.object_id

left JOIN 
    sys.tables AS tbl2
    ON fk.referenced_object_id = tbl2.object_id
    )
,
more AS
(
    SELECT 
        Table_name
        , Table_Schema
        , [ReferencedTable]
        , 0 AS [level]
        
    FROM 
        base
    WHERE 
        [ReferencedTable] IS NULL
    
    UNION ALL
    
    SELECT 
          b.Table_name
        , b.Table_Schema
        , b.[ReferencedTable]
        , m.[Level] + 1 AS [Level]
        
    FROM 
        base b
    INNER JOIN 
        more m
        ON    b.[ReferencedTable] = m.Table_Name 
        and      b.ReferencedTable <> b.Table_Name -- Fix to handle tables that are referencing themselfs
)
        
        SELECT DISTINCT
            * 
        FROM 
            more m
        WHERE NOT EXISTS(SELECT 1 FROM more m2 WHERE m2.level > m.level AND m2.table_name = m.table_name)
        ORDER BY 
            m.[level]
       --OPTION (MAXRECURSION 32767);


Example output
Table_name Table_Schema ReferencedTable level
AddressTypePerson 0
AWBuildVersiondbo 0
ContactPerson 0
ContactTypePerson 0
CountryRegionPerson 0
CreditCardSales 0
CultureProduction 0
CurrencySales 0
DatabaseLogdbo 0
DepartmentHumanResources 0
DocumentProduction 0
ErrorLogdbo 0
IllustrationProduction 0
LocationProduction 0
ProductCategoryProduction 0
ProductDescriptionProduction 0
ProductModelProduction 0
ProductPhotoProduction 0
SalesReasonSales 0
SalesTerritorySales 0
ScrapReasonProduction 0
ShiftHumanResources 0
ShipMethodPurchasing 0
SpecialOfferSales 0
TransactionHistoryArchiveProduction 0
UnitMeasureProduction 0
VendorPurchasing 0
ContactCreditCardSalesContact1
ContactCreditCardSalesCreditCard1
CountryRegionCurrencySalesCountryRegion1
CountryRegionCurrencySalesCurrency1
CurrencyRateSalesCurrency1
CustomerSalesSalesTerritory1
EmployeeHumanResourcesContact1
ProductModelIllustrationProductionIllustration1
ProductModelIllustrationProductionProductModel1
ProductModelProductDescriptionCultureProductionCulture1
ProductModelProductDescriptionCultureProductionProductDescription1
ProductModelProductDescriptionCultureProductionProductModel1
ProductSubcategoryProductionProductCategory1
StateProvincePersonCountryRegion1
StateProvincePersonSalesTerritory1
VendorContactPurchasingContact1
VendorContactPurchasingContactType1
VendorContactPurchasingVendor1
AddressPersonStateProvince2
EmployeeDepartmentHistoryHumanResourcesEmployee2
EmployeePayHistoryHumanResourcesEmployee2
IndividualSalesCustomer2
JobCandidateHumanResourcesEmployee2
ProductProductionProductSubcategory2
PurchaseOrderHeaderPurchasingEmployee2
SalesPersonSalesEmployee2
SalesTaxRateSalesStateProvince2
BillOfMaterialsProductionProduct3
CustomerAddressSalesAddress3
EmployeeAddressHumanResourcesAddress3
ProductCostHistoryProductionProduct3
ProductDocumentProductionProduct3
ProductInventoryProductionProduct3
ProductListPriceHistoryProductionProduct3
ProductProductPhotoProductionProduct3
ProductReviewProductionProduct3
ProductVendorPurchasingProduct3
PurchaseOrderDetailPurchasingProduct3
PurchaseOrderDetailPurchasingPurchaseOrderHeader3
SalesOrderHeaderSalesAddress3
SalesOrderHeaderSalesSalesPerson3
SalesPersonQuotaHistorySalesSalesPerson3
SalesTerritoryHistorySalesSalesPerson3
ShoppingCartItemSalesProduct3
SpecialOfferProductSalesProduct3
StoreSalesSalesPerson3
TransactionHistoryProductionProduct3
VendorAddressPurchasingAddress3
WorkOrderProductionProduct3
SalesOrderDetailSalesSalesOrderHeader4
SalesOrderDetailSalesSpecialOfferProduct4
SalesOrderHeaderSalesReasonSalesSalesOrderHeader4
StoreContactSalesStore4
WorkOrderRoutingProductionWorkOrder4
Comments