select all tables from a database

user generated tables
Select * from sysobjects where xtype='u'


system generated tables

Select * from sysobjects where xtype='s'