Query to get the names of all tables in SQL Server 2008 Database

Is it possible to write a query that will give me the names of all the tables in an SQL Server database? I'm working on some 'after the fact' documentation on a system I didn't create and am looking for a shortcut to 开发者_StackOverflow社区get a listing of the tables in a database.

In a single database - yes:

USE your_database
SELECT name FROM sys.tables

Getting all tables across all databases - only with a hack.... see this SO question for several approaches how to do that: How do I list all tables in all databases in SQL Server in a single result set?

another way, will also work on MySQL and PostgreSQL

select TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'

Try this:

SELECT s.NAME + '.' + t.NAME AS TableName
FROM sys.tables t
INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id

it will display the schema+table name for all tables in the current database.

Here is a version that will list every table in every database on the current server. it allows a search parameter to be used on any part or parts of the server+database+schema+table names:

SET NOCOUNT ON
DECLARE @AllTables table (CompleteTableName nvarchar(4000))
DECLARE @Search nvarchar(4000)
       ,@SQL   nvarchar(4000)
SET @Search=null --all rows
SET @SQL='select @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name from [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id WHERE @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name LIKE ''%'+ISNULL(@SEARCH,'')+'%'''

INSERT INTO @AllTables (CompleteTableName)
    EXEC sp_msforeachdb @SQL
SET NOCOUNT OFF
SELECT * FROM @AllTables ORDER BY 1

set @Search to NULL for all tables, set it to things like 'dbo.users' or 'users' or '.master.dbo' or even include wildcards like '.master.%.u', etc.

To get the fields info too, you can use the following:

SELECT TABLE_SCHEMA, TABLE_NAME, 
       COLUMN_NAME, substring(DATA_TYPE, 1,1) AS DATA_TYPE
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA NOT IN("information_schema", "mysql", "performance_schema") 
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

sys.tables

Contains all tables. so exec this query to get all tables with details.

SELECT * FROM sys.tables

or simply select Name from sys.tables to get the name of all tables.

SELECT Name From sys.tables

Please use the following query to list the tables in your DB.

select name from sys.Tables 

In Addition, you can add a where condition, to skip system generated tables and lists only user created table by adding type ='U'

Ex : select name from sys.Tables where type ='U'