EXEC sys.sp_tables

But this will list every single table, including all the system tables, which we rarely ever want. To list only tables created by the user, add a @table_owner parameter. For most databases, this will be "dbo":

EXEC sys.sp_tables NULL, 'dbo', NULL, NULL, NULL