Retrieve Subsidiary Businessunits (hierarchy) via T-SQL from CRM Database

USE CRM_MSCRM
GO

DECLARE @rootName NVARCHAR(255) = 'CRM' /* Parent Business Unit Name */

DECLARE @SubsidiaryBusinesseUnits TABLE

 (businessunitid UNIQUEIDENTIFIER PRIMARY KEY, depth INT)
DECLARE @depth INT = 0

INSERT @SubsidiaryBusinesseUnits
 SELECT businessunitid ,@depth FROM BusinessUnitBase WHERE Name = @rootName
WHILE (@@rowcount > 0)
BEGIN
 SELECT @depth = @depth + 1
 INSERT @SubsidiaryBusinesseUnits
 SELECT t.BusinessUnitId
  ,@depth
 FROM BusinessUnitBase t
 WHERE EXISTS (
   SELECT *
   FROM @SubsidiaryBusinesseUnits s
   WHERE s.depth = (@depth - 1)
    AND s.businessunitid = t.ParentBusinessUnitId
   )
END
SELECT * FROM @SubsidiaryBusinesseUnits

SAMPLE: CRM Business Unit Structure

SQL Result