Here's one way using dynamic SQL. There's not really any way to do this without iterating but this approach is much safer than undocumented, unsupported and buggy options like sp_MSforeachdb.

This will get a list of all online databases, the mapped user (if it exists) along with the default schema name and a comma-separated list of the roles they belong to.

DECLARE @name SYSNAME = N'your login name'; -- input param, presumably

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'UNION ALL SELECT N''' + REPLACE(name,'''','''''') + ''',
  p.name, p.default_schema_name, STUFF((SELECT N'','' + r.name 
  FROM ' + QUOTENAME(name) + N'.sys.database_principals AS r
  INNER JOIN ' + QUOTENAME(name) + N'.sys.database_role_members AS rm
   ON r.principal_id = rm.role_principal_id
  WHERE rm.member_principal_id = p.principal_id
  FOR XML PATH, TYPE).value(N''.[1]'',''nvarchar(max)''),1,1,N'''')
 FROM sys.server_principals AS sp
 LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
 ON sp.sid = p.sid
 WHERE sp.name = @name '
FROM sys.databases WHERE [state] = 0;

SET @sql = STUFF(@sql, 1, 9, N'');

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@name SYSNAME', @name;

Here's one way using dynamic SQL. There's not really any way to do this without iterating, but this approach is much safer than undocumented, unsupported and buggy options like sp_MSforeachdb (background here and here).

This will get a list of all online databases, the mapped user (if it exists), along with the default schema name, and a comma-separated list of the roles they belong to.

DECLARE @name sysname = N'your login name'; -- input param, presumably

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'UNION ALL SELECT N''' + REPLACE(name,'''','''''') + ''',
    p.name                 COLLATE SQL_Latin1_General_CP1_CI_AS, 
    p.default_schema_name  COLLATE SQL_Latin1_General_CP1_CI_AS, 
    STUFF((SELECT N'','' + r.name 
      FROM ' + QUOTENAME(name) + N'.sys.database_principals AS r
      INNER JOIN ' + QUOTENAME(name) + N'.sys.database_role_members AS rm
      ON r.principal_id = rm.role_principal_id
      WHERE rm.member_principal_id = p.principal_id
      FOR XML PATH, TYPE).value(N''.[1]'',''nvarchar(max)''),1,1,N'''')
    FROM sys.server_principals AS sp
    LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
    ON sp.sid = p.sid
    WHERE sp.name = @name '
  FROM sys.databases WHERE [state] = 0;

SET @sql = STUFF(@sql, 1, 9, N'');

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@name sysname', @name;

On more modern versions (2017+), I would still use dynamic SQL, but I would use STRING_AGG() instead of FOR XML PATH, probably something like this:

DECLARE @login sysname = N'your login name';

DECLARE @sql  nvarchar(max), 
        @sid  varbinary(85),
        @coll nvarchar(64) = N'COLLATE SQL_Latin1_General_CP1_CI_AS';

SELECT @sid = [sid] FROM sys.server_principals AS dp WHERE name = @login;

;WITH d AS 
(
  SELECT dbid = CONVERT(varchar(11), database_id),
         qn = QUOTENAME(name)
    FROM sys.databases WHERE [state] = 0
)
SELECT @sql = STRING_AGG(CONVERT(nvarchar(max),
        N'SELECT db = d.name, username = dp.name ' + @coll + ', 
        schemaname = dp.default_schema_name ' + @coll + ',
        roles = STRING_AGG(rp.name ' + @coll + ', N'','')
        FROM sys.databases AS d
        LEFT OUTER JOIN ' + qn + '.sys.database_principals AS dp ON dp.sid = @sid
        LEFT OUTER JOIN ' + qn + '.sys.database_role_members AS rm
        ON dp.principal_id = rm.member_principal_id
        LEFT OUTER JOIN ' + qn + '.sys.database_principals AS rp
        ON rp.principal_id = rm.role_principal_id
        WHERE d.database_id = ' + dbid + N'
        GROUP BY d.name, dp.name, dp.default_schema_name'
    ), char(13) + char(10) + N' UNION ALL ')
FROM d;

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@sid varbinary(85)', @sid;

In this latter example, if you only want the databases with a user mapped to the named login, just change the first left join to an inner join.

Here's one way using dynamic SQL. There's not really any way to do this without iterating but this approach is much safer than undocumented, unsupported and buggy options like sp_MSforeachdb.

This will get a list of all online databases, the mapped user (if it exists) along with the default schema name and a comma-separated list of the roles they belong to.

DECLARE @name SYSNAME = N'your login name'; -- input param, presumably

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'UNION ALL SELECT N''' + REPLACE(name,'''','''''') + ''',
  p.name, p.default_schema_name, STUFF((SELECT N'','' + r.name 
  FROM sys.database_principals AS r
  INNER JOIN sys.database_role_members AS rm
   ON r.principal_id = rm.role_principal_id
  WHERE rm.member_principal_id = p.principal_id
  FOR XML PATH, TYPE).value(N''.[1]'',''nvarchar(max)''),1,1,N'''')
 FROM sys.server_principals AS sp
 LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
 ON sp.sid = p.sid
 WHERE sp.name = @name '
FROM sys.databases WHERE [state] = 0;

SET @sql = STUFF(@sql, 1, 9, N'');

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@name SYSNAME', @name;

Here's one way using dynamic SQL. There's not really any way to do this without iterating but this approach is much safer than undocumented, unsupported and buggy options like sp_MSforeachdb.

This will get a list of all online databases, the mapped user (if it exists) along with the default schema name and a comma-separated list of the roles they belong to.

DECLARE @name SYSNAME = N'your login name'; -- input param, presumably

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'UNION ALL SELECT N''' + REPLACE(name,'''','''''') + ''',
  p.name, p.default_schema_name, STUFF((SELECT N'','' + r.name 
  FROM ' + QUOTENAME(name) + N'.sys.database_principals AS r
  INNER JOIN ' + QUOTENAME(name) + N'.sys.database_role_members AS rm
   ON r.principal_id = rm.role_principal_id
  WHERE rm.member_principal_id = p.principal_id
  FOR XML PATH, TYPE).value(N''.[1]'',''nvarchar(max)''),1,1,N'''')
 FROM sys.server_principals AS sp
 LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
 ON sp.sid = p.sid
 WHERE sp.name = @name '
FROM sys.databases WHERE [state] = 0;

SET @sql = STUFF(@sql, 1, 9, N'');

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@name SYSNAME', @name;