SQL Query to Get List of All Child Tables for a Given Table

CREATE PROCEDURE spGetChildTables
(
    @masterTableName varchar(1000) = 'Dim_Accounts'
)
AS
BEGIN
    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 * FROM @ScannedTables
END


Example 2

CREATE PROCEDURE [dbo].[spGetChildTables]
(
    @masterTableName varchar(1000) = 'Dim_Accounts'
)
AS
BEGIN
    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 * FROM @ScannedTables ST
    JOIN
    (
        SELECT
            c.CONSTRAINT_NAME
            , cu.TABLE_NAME AS ReferencingTable
            , cu.COLUMN_NAME AS ReferencingColumn
            , ku.TABLE_NAME AS ReferencedTable
            , ku.COLUMN_NAME AS ReferencedColumn
            , cu.TABLE_NAME + '.' + cu.COLUMN_NAME + ' = ' + ku.TABLE_NAME + '.' + ku.COLUMN_NAME AS JoinCondition
        FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
        INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
        ON cu.CONSTRAINT_NAME = c.CONSTRAINT_NAME
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
        ON ku.CONSTRAINT_NAME = c.UNIQUE_CONSTRAINT_NAME
    ) A
    ON A.ReferencingTable COLLATE Latin1_General_CI_AS = ST.Name    
END