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

Question

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

Answer 1

exec sp_msforeachtable 'print ''?'''

Answer 2

select * from sysobjects where xtype='U'

Answer 3

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

(sql2000 standard, still supported in 2005)

Answer 4

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

Answer 5

SELECT * FROM INFORMATION_SCHEMA.TABLES

or Sys.Tables

...all these sysobjects queries, and I thought I was old school =)

Answer 6

SQL Server 2005 or 2008:

SELECT * FROM information_schema.tables

SQL Server 2000:

SELECT * FROM sysobjects WHERE xtype='U'

Answer 7

How can I do the equivalent of “SHOW TABLES” in T-SQL?

Answer 8

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

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

• C: Check constraint
• D: Default constraint
• F: Foreign Key constraint
• L: Log
• P: Stored procedure
• PK: Primary Key constraint
• RF: Replication Filter stored procedure
• S: System table
• TR: Trigger
• U: User table
• UQ: Unique constraint
• V: View
• X: Extended stored procedure

Answer 9

http://stackoverflow.com/questions/465014/list-table-names

http://stackoverflow.com/questions/420741/getting-list-of-tables-and-fields-in-each-in-a-database

http://stackoverflow.com/questions/454986/is-there-a-quick-way-to-report-database-metadata-in-sql-server-2005