List of role permissions including other roles - how can I find out the roles that belong to other roles?
;WITH theRoles (member_principal_id, role_principal_id) 
AS 
(
  SELECT 
   r.member_principal_id, 
   r.role_principal_id
  FROM sys.database_role_members r 
   UNION ALL
  SELECT 
   tr.member_principal_id, 
   rm.role_principal_id
  FROM sys.database_role_members rm 
            INNER JOIN theRoles tr 
                    ON rm.member_principal_id = tr.role_principal_id
)



select  the_role=A.name
       ,A.is_fixed_role
       ,role_member=B.name
       ,B.type_desc
from theRoles tr
        INNER JOIN sys.database_principals A 
                ON tr.role_principal_id = A.principal_id
        INNER JOIN sys.database_principals B 
                ON tr.member_principal_id = B.principal_id
GROUP BY A.name
       ,A.is_fixed_role
       ,B.name
       ,B.type_desc
order by a.is_fixed_role DESC
        ,a.name


and for testing the script above:

USE [DBA]
GO
CREATE ROLE [role_1]
GO
CREATE ROLE [role_2]
GO
CREATE ROLE [role_3]
GO
CREATE ROLE [role_10]
GO
CREATE ROLE [role_101]
GO
CREATE ROLE [role_1010]
GO

sp_addrolemember @rolename='db_datareader',@membername='role_1'
go
sp_addrolemember @rolename='db_datareader',@membername='role_2'
go
sp_addrolemember @rolename='db_datareader',@membername='role_3'
go
sp_addrolemember @rolename='role_1',@membername='role_10'
go
sp_addrolemember @rolename='role_10',@membername='role_101'
go
sp_addrolemember @rolename='role_10',@membername='role_1010'
go
sp_addrolemember @rolename='role_101',@membername='role_1010'
go