SELECT
            S.schema_id,
            S.name AS schema_name,
            COUNT(O.OBJECT_ID) AS objects_in_schema
    FROM
            sys.schemas S JOIN
            sys.all_objects O ON
            S.schema_id = O.schema_id
    GROUP BY
            S.schema_id,
            S.name