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

Question

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 get a listing of the tables in a database.

Answer 1

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: http://stackoverflow.com/questions/2875768/how-do-i-list-all-tables-in-all-databases-in-sql-server-in-a-single-result-set

Answer 2

another way, will also work on MySQL and PostgreSQL

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

Answer 3

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.