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 |
AddressType | Person | | 0 |
AWBuildVersion | dbo | | 0 |
Contact | Person | | 0 |
ContactType | Person | | 0 |
CountryRegion | Person | | 0 |
CreditCard | Sales | | 0 |
Culture | Production | | 0 |
Currency | Sales | | 0 |
DatabaseLog | dbo | | 0 |
Department | HumanResources | | 0 |
Document | Production | | 0 |
ErrorLog | dbo | | 0 |
Illustration | Production | | 0 |
Location | Production | | 0 |
ProductCategory | Production | | 0 |
ProductDescription | Production | | 0 |
ProductModel | Production | | 0 |
ProductPhoto | Production | | 0 |
SalesReason | Sales | | 0 |
SalesTerritory | Sales | | 0 |
ScrapReason | Production | | 0 |
Shift | HumanResources | | 0 |
ShipMethod | Purchasing | | 0 |
SpecialOffer | Sales | | 0 |
TransactionHistoryArchive | Production | | 0 |
UnitMeasure | Production | | 0 |
Vendor | Purchasing | | 0 |
ContactCreditCard | Sales | Contact | 1 |
ContactCreditCard | Sales | CreditCard | 1 |
CountryRegionCurrency | Sales | CountryRegion | 1 |
CountryRegionCurrency | Sales | Currency | 1 |
CurrencyRate | Sales | Currency | 1 |
Customer | Sales | SalesTerritory | 1 |
Employee | HumanResources | Contact | 1 |
ProductModelIllustration | Production | Illustration | 1 |
ProductModelIllustration | Production | ProductModel | 1 |
ProductModelProductDescriptionCulture | Production | Culture | 1 |
ProductModelProductDescriptionCulture | Production | ProductDescription | 1 |
ProductModelProductDescriptionCulture | Production | ProductModel | 1 |
ProductSubcategory | Production | ProductCategory | 1 |
StateProvince | Person | CountryRegion | 1 |
StateProvince | Person | SalesTerritory | 1 |
VendorContact | Purchasing | Contact | 1 |
VendorContact | Purchasing | ContactType | 1 |
VendorContact | Purchasing | Vendor | 1 |
Address | Person | StateProvince | 2 |
EmployeeDepartmentHistory | HumanResources | Employee | 2 |
EmployeePayHistory | HumanResources | Employee | 2 |
Individual | Sales | Customer | 2 |
JobCandidate | HumanResources | Employee | 2 |
Product | Production | ProductSubcategory | 2 |
PurchaseOrderHeader | Purchasing | Employee | 2 |
SalesPerson | Sales | Employee | 2 |
SalesTaxRate | Sales | StateProvince | 2 |
BillOfMaterials | Production | Product | 3 |
CustomerAddress | Sales | Address | 3 |
EmployeeAddress | HumanResources | Address | 3 |
ProductCostHistory | Production | Product | 3 |
ProductDocument | Production | Product | 3 |
ProductInventory | Production | Product | 3 |
ProductListPriceHistory | Production | Product | 3 |
ProductProductPhoto | Production | Product | 3 |
ProductReview | Production | Product | 3 |
ProductVendor | Purchasing | Product | 3 |
PurchaseOrderDetail | Purchasing | Product | 3 |
PurchaseOrderDetail | Purchasing | PurchaseOrderHeader | 3 |
SalesOrderHeader | Sales | Address | 3 |
SalesOrderHeader | Sales | SalesPerson | 3 |
SalesPersonQuotaHistory | Sales | SalesPerson | 3 |
SalesTerritoryHistory | Sales | SalesPerson | 3 |
ShoppingCartItem | Sales | Product | 3 |
SpecialOfferProduct | Sales | Product | 3 |
Store | Sales | SalesPerson | 3 |
TransactionHistory | Production | Product | 3 |
VendorAddress | Purchasing | Address | 3 |
WorkOrder | Production | Product | 3 |
SalesOrderDetail | Sales | SalesOrderHeader | 4 |
SalesOrderDetail | Sales | SpecialOfferProduct | 4 |
SalesOrderHeaderSalesReason | Sales | SalesOrderHeader | 4 |
StoreContact | Sales | Store | 4 |
WorkOrderRouting | Production | WorkOrder | 4 |
|
|