アンドキュメンテッド ストアドプロシージャ(非公開のストアドプロシージャ)を利用すれば、指定したフォルダのサブフォルダ名とその階層の深さを取得することができます。
これを実現するためには、「xp_dirtree」を利用します。

【構文】
EXEC xp_dirtree 'フォルダパス'


結果として、subdirectory(サブフォルダ名)とdepth(階層の深さ)を取得できます。
指定したフォルダパスが存在しない場合には、結果は0件となります。
(エラーは発生しません)

以下、サンプル。
EXEC xp_dirtree 'C:\Windows'


【補足事項】
・結果は、深さ優先探索の順で表示されます。
 深さ優先探索については、こちら(深さ優先探索 - Wikipedia)を参照して下さい。