Also available under System Views is the INFORMATION_SCHEMA collection which can give you all the information you need for any metadata in the the database.

For listing out your table collection for example:

SELECT * FROM

INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

This will list every table in the target database.