Reputation: 1253
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.
Upvotes: 32
Views: 202995
Reputation: 11
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'
Upvotes: 0
Reputation: 3243
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
Upvotes: 0
Reputation: 209
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
Upvotes: 1
Reputation: 103697
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.
Upvotes: 6
Reputation: 135171
another way, will also work on MySQL and PostgreSQL
select TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
Upvotes: 9
Reputation: 755361
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?
Upvotes: 55