Cantor was interested in, and developed, a theory of ordinal numbers and cardinalities. This was a significant accomplishment, in part because it overcame traditional resistance to working with actual infinity. Cantor's theory did not by any means become the foundation of mathematics (though it is certainly studied extensively in modern set theory). The axiomatisation provided by Zermelo and Fraenkel uses the language of sets, but the role of ordinals and cardinals is tangential. So not only did Cantor not think that his "theory could provide a foundation for all of mathematics", in point of fact it doesn't.
This recent article by Rowe on Cantor, Dedekind, and Hilbert suggests that both Cantor and Hilbert did think of set theory as foundation for real analysis. Cantor and Hilbert were both bothered by foundational problems in set theory (such as the fact that all aleph-numbers can't form a set without causing a contradiction), and hoped to sort this out as a way of providing a solid foundation for the set of real numbers.