select name
from sysobjects
where xtype = 'U'
[download]

For the sake of portability it might also be useful to note that ANSI SQL-92 provides a standard for querying table schema information:

select TABLE_NAME from INFORMATION_SCHEMA.TABLES

See also this discussion started by gmax, particularly this node which has more relevant info.