SQL Script to find all Child table upto Nth Level in SQL Server

I found out this using CTE(Common Table Expression). May not be the best but might help guess. Say there is a MasterTable A and its has three child Tables B C D. Now again these Childtable may act as Master to Someother Child Tables say B1 B2 C1 C2 D1 D1 where B1 B2 are child tables of B and So on.

In this this Script will provide you the Hirearchy Level as well.

DECLARE @MasterTableName AS VARCHAR(255)='YourMasterTableName'
;WITH  YOURCTENAME AS

(

--initialization

SELECT object_name(referenced_object_id) as ParentTable, object_name(parent_object_id) as ChildTable, 1 as hlevel

FROM sys.foreign_keys

WHERE object_name(referenced_object_id) =@MasterTableName

UNION ALL

--recursive execution

SELECT object_name(e.referenced_object_id), object_name(e.parent_object_id),m.hlevel + 1

FROM sys.foreign_keys e INNER JOIN YOURCTENAME m

ON object_name(e.referenced_object_id) = m.ChildTable --.object_name(parent_object_id)

and not (m.ChildTable = object_name(e.parent_object_id) and m.ParentTable = object_name(e.referenced_object_id)) ----Used this because Might Go in Infinite if some table is self Referenced

)

SELECT distinct childtable, hlevel FROM YOURCTENAME

order by hlevel desc

option (maxrecursion 32767);

Some Important useful SQL Scripts for SQL Server.

1. Generate update statement for multiple Rows using values from exisiting Table.

SELECT '
UPDATE TABLENAME
SET COLUMNNAME = ''' + COLUMNNAME+ '''
WHERE  KEYCOLUMNNAME= ' + CAST(KEYCOLUMNNAME AS VARCHAR(5))
FROM
Get result in Text - (Cntr+T)

Ex:
SELECT '
UPDATE Table1
SET Column1 = ''' + Column1 + '''
WHERE ColumnUnique = ' + CAST(ColumnUnique AS VARCHAR(5))
FROM Table1

2. To get the original DB name from which it is restored.

SELECT
org.database_name Org_DBName,
Restored_To_DBName,
Last_Date_Restored
FROM
msdb..backupset org
INNER JOIN
(
SELECT
backup_set_id,
Restored_To_DBName,
Last_Date_Restored
FROM
msdb..restorehistory
INNER JOIN
(
SELECT
rh.destination_database_name Restored_To_DBName,
Max(rh.restore_date) Last_Date_Restored
FROM
msdb..restorehistory rh
GROUP BY
rh.destination_database_name
) AS InnerRest
ON
destination_database_name = Restored_To_DBName AND
restore_date = Last_Date_Restored
) As RestData
ON
org.backup_set_id = RestData.backup_set_id;

Any Suggestion and Better things always Welcome :)