SQL Server, Finding dependent tables recursively

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:-

http://msdn.microsoft.com/en-us/library/aa175805(SQL.80).aspx

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

Following can also help:-

DECLARE @masterTableName varchar(1000)

SET @masterTableName = 'Companies'

 

DECLARE @ScannedTables TABLE( Level int, Name varchar(1000) collate Latin1_General_CI_AS )

 

DECLARE @currentTableCount INT

DECLARE @previousTableCount INT

DECLARE @level INT

 

SET @currentTableCount = 0

SET @previousTableCount = -1

SET @level = 0

 

INSERT INTO @ScannedTables VALUES ( @level, @masterTableName )

 

WHILE @previousTableCount <> @currentTableCount

BEGIN

 

    SET @previousTableCount = @currentTableCount

 

    INSERT INTO @ScannedTables

 

        SELECT DISTINCT

            @level + 1, TC.Table_Name COLLATE Latin1_General_CI_AS

 

        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC

        LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON TC.Constraint_Name = RC.Constraint_Name

        LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FTC ON RC.Unique_Constraint_Name = FTC.Constraint_Name

 

        WHERE TC.CONSTRAINT_TYPE = 'FOREIGN KEY'

 

        AND FTC.TABLE_NAME COLLATE Latin1_General_CI_AS IN ( SELECT Name FROM @ScannedTables WHERE Level = @level )

        AND TC.Table_Name COLLATE Latin1_General_CI_AS NOT IN ( SELECT Name FROM @ScannedTables )

 

    SET @level = @level + 1

 

    SELECT @currentTableCount = COUNT(*) FROM @ScannedTables  

END

 

SELECT [Level], 'DELETE * FROM ' + Name FROM @ScannedTables ORDER BY [Level] DESC

 

From http://stackoverflow.com/questions/3441251/how-to-get-list-of-child-tables-for-a-database-table