How do I get list of all tables in a database using TSQL?

What is the best way to get the names of all of the tables in a specific database on SQL Server?

exec sp_msforeachtable 'print ''?'''

`select * from sysobjects where xtype='U'`

SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'

SELECT name
FROM sysobjects
WHERE xtype='U'
ORDER BY name;

(SQL Server 2000 standard; still supported in SQL Server 2005.)

SELECT * FROM information_schema.tables
where TABLE_TYPE = 'BASE TABLE'
SQL Server 2012

SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'

Here is a list of other object types you can search for as well:

- AF: Aggregate function (CLR)
- C: CHECK constraint
- D: Default or DEFAULT constraint
- F: FOREIGN KEY constraint
- L: Log
- FN: Scalar function
- FS: Assembly (CLR) scalar-function
- FT: Assembly (CLR) table-valued function
- IF: In-lined table-function
- IT: Internal table
- P: Stored procedure
- PC: Assembly (CLR) stored-procedure
- PK: PRIMARY KEY constraint (type is K)
- RF: Replication filter stored procedure
- S: System table
- SN: Synonym
- SQ: Service queue
- TA: Assembly (CLR) DML trigger
- TF: Table function
- TR: SQL DML Trigger
- TT: Table type
- U: User table
- UQ: UNIQUE constraint (type is K)
- V: View
- X: Extended stored procedure

--for oracle
select tablespace_name, table_name from all_tables;

---------------------------------------------
This link can provide much more information on this
[topic][1]




[1]:

[To see links please register here]

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_NAME

Thanks to Ray Vega, whose response gives all user tables in a database...

> exec sp_msforeachtable 'print ''?'''

sp_helptext shows the underlying query, which summarises to...

select * from dbo.sysobjects o
join sys.all_objects syso on o.id = syso.object_id
where OBJECTPROPERTY(o.id, 'IsUserTable') = 1
and o.category & 2 = 0