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

The downside of INFORMATION_SCHEMA.TABLES is that it also includes system tables such as dtproperties and the MSpeer_... tables, with no way to tell them apart from your own tables.

I would recommend using sys.objects (the new version of the deprecated sysobjects view), which does support excluding the system tables:

select *
from sys.objects
where type = 'U'      -- User tables
and is_ms_shipped = 0 -- Exclude system tables

Answer 2

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

SQL Server 2012

Answer 3

select * from sysobjects where xtype='U'

Answer 4

Please use this. You will get table names along with schema names:

SELECT SYSSCHEMA.NAME, SYSTABLE.NAME
FROM SYS.tables SYSTABLE
INNER JOIN SYS.SCHEMAS SYSSCHEMA
ON SYSTABLE.SCHEMA_ID = SYSSCHEMA.SCHEMA_ID

Answer 5

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