SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'TABLE'
Side comment: I generally suggest people do not use * in lieu of an actual column list, because of added or removed columns, and possible renames or reordering. Though, that could technically happen here, it is unlikely on an information_schema table. So, i'd still suggest it, just not as strongly.
OTOH, it is also self-documenting and creates less traffic over the network when you only ask for the actual columns you need. At the very least, you do not need TABLE_TYPE in this query since it is specified with a literal anyway.