What is the best way to get the names of all of the tables in a specific database on SQL Server?
This question is related to
sql-server
tsql
database-table
exec sp_msforeachtable 'print ''?'''
Using SELECT * FROM INFORMATION_SCHEMA.COLUMNS also shows you all tables and related columns.
Well you can use sys.objects to get all database objects.
GO
select * from sys.objects where type_desc='USER_TABLE' order by name
GO
OR
-- For all tables
select * from INFORMATION_SCHEMA.TABLES
GO
--- For user defined tables
select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE'
GO
--- For Views
select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='VIEW'
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES
OR
SELECT * FROM Sys.Tables
select * from sysobjects where xtype='U'
SELECT name
FROM sysobjects
WHERE xtype='U'
ORDER BY name;
(SQL Server 2000 standard; still supported in SQL Server 2005.)
SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'
SELECT * FROM information_schema.tables
where TABLE_TYPE = 'BASE TABLE'
SQL Server 2012
--for oracle
select tablespace_name, table_name from all_tables;
This link can provide much more information on this topic
select * from sysobjects where xtype='U'
SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'
Here is a list of other object types you can search for as well:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_NAME
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
SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'
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
SELECT name
FROM sysobjects
WHERE xtype='U'
ORDER BY name;
(SQL Server 2000 standard; still supported in SQL Server 2005.)
SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'
exec sp_msforeachtable 'print ''?'''
SELECT * FROM information_schema.tables
where TABLE_TYPE = 'BASE TABLE'
SQL Server 2012
SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'
SELECT name
FROM sysobjects
WHERE xtype='U'
ORDER BY name;
(SQL Server 2000 standard; still supported in SQL Server 2005.)
Well you can use sys.objects to get all database objects.
GO
select * from sys.objects where type_desc='USER_TABLE' order by name
GO
OR
-- For all tables
select * from INFORMATION_SCHEMA.TABLES
GO
--- For user defined tables
select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE'
GO
--- For Views
select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='VIEW'
GO
select * from sysobjects where xtype='U'
USE YourDBName
GO
SELECT *
FROM sys.Tables
GO
OR
USE YourDBName
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES
GO
SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'
Here is a list of other object types you can search for as well:
SELECT * FROM INFORMATION_SCHEMA.TABLES
OR
SELECT * FROM Sys.Tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_NAME
exec sp_msforeachtable 'print ''?'''
SELECT * FROM INFORMATION_SCHEMA.TABLES
OR
SELECT * FROM Sys.Tables
Thanks to Ray Vega, whose response gives all user tables in a database...
exec sp_msforeachtable 'print ''?'''
sp_helptext shows the underlying query, which summarises to...
select * from dbo.sysobjects o
join sys.all_objects syso on o.id = syso.object_id
where OBJECTPROPERTY(o.id, 'IsUserTable') = 1
and o.category & 2 = 0
Using SELECT * FROM INFORMATION_SCHEMA.COLUMNS also shows you all tables and related columns.
SELECT name
FROM sysobjects
WHERE xtype='U'
ORDER BY name;
(SQL Server 2000 standard; still supported in SQL Server 2005.)
SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'
Here is a list of other object types you can search for as well:
In SSMS, to get all fully qualified table names in a specific database (E.g., "MyDatabase"):
SELECT [TABLE_CATALOG] + '.' + [TABLE_SCHEMA] + '.' + [TABLE_NAME]
FROM MyDatabase.INFORMATION_SCHEMA.Tables
WHERE [TABLE_TYPE] = 'BASE TABLE' and [TABLE_NAME] <> 'sysdiagrams'
ORDER BY [TABLE_SCHEMA], [TABLE_NAME]
Results:
In SSMS, to get all fully qualified table names in a specific database (E.g., "MyDatabase"):
SELECT [TABLE_CATALOG] + '.' + [TABLE_SCHEMA] + '.' + [TABLE_NAME]
FROM MyDatabase.INFORMATION_SCHEMA.Tables
WHERE [TABLE_TYPE] = 'BASE TABLE' and [TABLE_NAME] <> 'sysdiagrams'
ORDER BY [TABLE_SCHEMA], [TABLE_NAME]
Results:
SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'
Here is a list of other object types you can search for as well:
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
USE YourDBName
GO
SELECT *
FROM sys.Tables
GO
OR
USE YourDBName
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES
GO
--for oracle
select tablespace_name, table_name from all_tables;
This link can provide much more information on this topic
Thanks to Ray Vega, whose response gives all user tables in a database...
exec sp_msforeachtable 'print ''?'''
sp_helptext shows the underlying query, which summarises to...
select * from dbo.sysobjects o
join sys.all_objects syso on o.id = syso.object_id
where OBJECTPROPERTY(o.id, 'IsUserTable') = 1
and o.category & 2 = 0
exec sp_msforeachtable 'print ''?'''
SELECT * FROM INFORMATION_SCHEMA.TABLES
OR
SELECT * FROM Sys.Tables
Source: Stackoverflow.com