SQL to check how many sites a user is a member of

I have a multisite installation and I’m working on creating a reporting plugin. One of the things that I would like to do is show the number of sites a user is a member of.

Right now, I’m using something like this in my SQL:

JOIN `wp_usermeta` m ON m.user_id = u.ID AND ( `meta_key` = 'wp_capabilities' OR `meta_key` LIKE 'wp_%_capabilities' )

I’m not comfortable using the % wildcard in that syntax. It could easily end up selecting any meta value that ends in capabilities.

Besides the alternative (listing each site with an AND in the subquery) – is there a way to select just wp_##_capabilities in SQL?

You could simply use the get_blogs_of_user() function:

echo count( get_blogs_of_user( $user_ID ) );

instead of your custom SQL query.