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