How do I get list of all tables in a database usin

Question

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

Answer 1

SELECT * FROM INFORMATION_SCHEMA.TABLES 

or

SELECT * FROM Sys.Tables

Answer 2

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

Answer 3

you can simply select your database first

use database_name;

then just type

show tables;

Answer 4

--for oracle
select tablespace_name, table_name from all_tables;

---

This link can provide much more information on this topic

Answer 5

exec sp_msforeachtable 'print ''?'''

Answer 6

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