Wanted to export few tables to new db. For this I needed to find out the tables on which given table 'X' depends, and then tables on which those tables depends...(problem sound recursive)
Step 1. Create view:- REFERENTIAL_CONSTRAINTS_COLUMN_USAGE as described here:-
Step2. Write CTE Query to recurse through tree of all tables related to given table 'X'
WITH FindTableTree (TableName,HLevel)
AS
(
SELECT UNIQUE_TABLE_NAME, 0 AS HLevel from dbo.REFERENTIAL_CONSTRAINTS_COLUMN_USAGE rc
WHERE rc.TABLE_NAME = 'X'
union all
SELECT UNIQUE_TABLE_NAME, HLevel +1 from FindTableTree, dbo.REFERENTIAL_CONSTRAINTS_COLUMN_USAGE rc
WHERE rc.TABLE_NAME = FindTableTree.TableName
)
SELECT DISTINCT TableName
FROM FindTableTree
GO
=============================================================
For some odd SQL server R2 i could not create any new view in inofmration_schema. Please use following looong query for same purpose.
WITH FindTableTree (TableName,HLevel)
AS
(
SELECT
KCU2.TABLE_NAME AS UNIQUE_TABLE_NAME
, 0 AS HLevel
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU1.CONSTRAINT_SCHEMA =
RC.CONSTRAINT_SCHEMA
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
ON KCU2.CONSTRAINT_CATALOG =
RC.UNIQUE_CONSTRAINT_CATALOG
AND KCU2.CONSTRAINT_SCHEMA =
RC.UNIQUE_CONSTRAINT_SCHEMA
AND KCU2.CONSTRAINT_NAME =
RC.UNIQUE_CONSTRAINT_NAME
WHERE KCU1.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
AND KCU1.TABLE_NAME = 'CustomerDetail'
union all
SELECT KCU2.TABLE_NAME, HLevel +1 FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU1.CONSTRAINT_SCHEMA =
RC.CONSTRAINT_SCHEMA
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
ON KCU2.CONSTRAINT_CATALOG =
RC.UNIQUE_CONSTRAINT_CATALOG
AND KCU2.CONSTRAINT_SCHEMA =
RC.UNIQUE_CONSTRAINT_SCHEMA
AND KCU2.CONSTRAINT_NAME =
RC.UNIQUE_CONSTRAINT_NAME inner join FindTableTree on
KCU1.TABLE_NAME = FindTableTree.TableName
)
SELECT DISTINCT TableName,HLevel
FROM FindTableTree
GO
