This topic will show you how to use sql query to list all tables in a database using TSQL. You can easily get list of all tables by using sql query to the SYS.SYSOBJECTS metadata view.
SELECT * FROM SYSOBJECTS WHERE xtype = 'U'
The SYS.SYSOBJECTS contains a row for every object created in the database, including stored procedures, views and user tables.
If you want to return all tables and views in one query, you can execute the following TSQL statement.
SELECT * FROM INFORMATION_SCHEMA.TABLES
If you want to retrieve actual tables and filter out views from the results, you can add a WHERE TABLE_TYPE = 'BASE TABLE' clause:
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Listing all tables in a database when using a newer sql 2005 version or later is a matter of querying INFORMATION_SCHEMA views that are automatically integrated into SQL Server.