DBI & ODBC are very handy when dealing with SQL Server (I use it whenever possible), but in the spirit of TMTOWTDI --

In the database, there is a table called sysobjects that stores information about all the objects in that database. Each object type (key, table, index, etc.) has a specific code. These codes are stored in the xtype field. So...

SELECT name
FROM sysobjects
WHERE xtype='U'
ORDER BY name
[download]

... should return a list of all table names in the database.

Cheers