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

User · Answer

USE YourDBName GO  SELECT   FROM sys Tables GO   OR  USE YourDBName GO SELECT   FROM INFORMATION SCHEMA TABLES  GO

User · Answer

SELECT sobjects name FROM sysobjects sobjects WHERE sobjects xtype    U

User · Answer

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   lt  gt   sysdiagrams  ORDER BY  TABLE SCHEMA    TABLE NAME    Results    MyDatabase dbo MyTable1  MyDatabase dbo MyTable2 MyDatabase MySchema MyTable3  MyDatabase MySchema MyTable4 etc

User · Answer

USE YourDBName GO  SELECT   FROM sys Tables GO   OR  USE YourDBName GO SELECT   FROM INFORMATION SCHEMA TABLES  GO

User · Answer

exec sp msforeachtable  print

User · Answer

SELECT   FROM INFORMATION SCHEMA TABLES    OR  SELECT   FROM Sys Tables

User · Answer

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

User · Answer

SELECT sobjects name FROM sysobjects sobjects WHERE sobjects xtype    U    Here is a list of other object types you can search for as well    AF  Aggregate function  CLR  C  CHECK constraint D  Default or DEFAULT constraint F  FOREIGN KEY constraint L  Log FN  Scalar function FS  Assembly  CLR  scalar-function FT  Assembly  CLR  table-valued function IF  In-lined table-function IT  Internal table P  Stored procedure PC  Assembly  CLR  stored-procedure PK  PRIMARY KEY constraint  type is K  RF  Replication filter stored procedure S  System table SN  Synonym SQ  Service queue TA  Assembly  CLR  DML trigger TF  Table function TR  SQL DML Trigger TT  Table type U  User table UQ  UNIQUE constraint  type is K  V  View X  Extended stored procedure

User · Answer

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

User · Answer

SELECT name  FROM sysobjects  WHERE xtype  U   ORDER BY name     SQL Server 2000 standard  still supported in SQL Server 2005

User · Answer

select   from sysobjects where xtype  U

User · Answer

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

User · Answer

SELECT   FROM INFORMATION SCHEMA TABLES    OR  SELECT   FROM Sys Tables

User · Answer

SQL Server 2000  2005  2008  2012  2014  2016  2017 or 2019  SELECT   FROM INFORMATION SCHEMA TABLES WHERE TABLE TYPE  BASE TABLE   To show only tables from a particular database SELECT TABLE NAME  FROM   lt DATABASE NAME gt   INFORMATION SCHEMA TABLES  WHERE TABLE TYPE    BASE TABLE   Or  SELECT TABLE NAME  FROM INFORMATION SCHEMA TABLES  WHERE TABLE TYPE    BASE TABLE       AND TABLE CATALOG  dbName  -- for MySql  use  TABLE SCHEMA  dbName     PS  For SQL Server 2000  SELECT   FROM sysobjects WHERE xtype  U

User · Answer

select   from sysobjects where xtype  U

User · Answer

SELECT sobjects name FROM sysobjects sobjects WHERE sobjects xtype    U    Here is a list of other object types you can search for as well    AF  Aggregate function  CLR  C  CHECK constraint D  Default or DEFAULT constraint F  FOREIGN KEY constraint L  Log FN  Scalar function FS  Assembly  CLR  scalar-function FT  Assembly  CLR  table-valued function IF  In-lined table-function IT  Internal table P  Stored procedure PC  Assembly  CLR  stored-procedure PK  PRIMARY KEY constraint  type is K  RF  Replication filter stored procedure S  System table SN  Synonym SQ  Service queue TA  Assembly  CLR  DML trigger TF  Table function TR  SQL DML Trigger TT  Table type U  User table UQ  UNIQUE constraint  type is K  V  View X  Extended stored procedure

User · Answer

SELECT TABLE NAME  FROM INFORMATION SCHEMA TABLES  WHERE TABLE TYPE  BASE TABLE   ORDER BY TABLE NAME

User · Answer

SELECT   FROM INFORMATION SCHEMA TABLES    OR  SELECT   FROM Sys Tables

User · Answer

SQL Server 2000  2005  2008  2012  2014  2016  2017 or 2019  SELECT   FROM INFORMATION SCHEMA TABLES WHERE TABLE TYPE  BASE TABLE   To show only tables from a particular database SELECT TABLE NAME  FROM   lt DATABASE NAME gt   INFORMATION SCHEMA TABLES  WHERE TABLE TYPE    BASE TABLE   Or  SELECT TABLE NAME  FROM INFORMATION SCHEMA TABLES  WHERE TABLE TYPE    BASE TABLE       AND TABLE CATALOG  dbName  -- for MySql  use  TABLE SCHEMA  dbName     PS  For SQL Server 2000  SELECT   FROM sysobjects WHERE xtype  U

User · Answer

SELECT TABLE NAME  FROM INFORMATION SCHEMA TABLES  WHERE TABLE TYPE  BASE TABLE   ORDER BY TABLE NAME

User · Answer

Using SELECT   FROM INFORMATION SCHEMA COLUMNS also shows you all tables and related columns

User · Answer

select   from sysobjects where xtype  U

User · Answer

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

User · Answer

SQL Server 2000  2005  2008  2012  2014  2016  2017 or 2019  SELECT   FROM INFORMATION SCHEMA TABLES WHERE TABLE TYPE  BASE TABLE   To show only tables from a particular database SELECT TABLE NAME  FROM   lt DATABASE NAME gt   INFORMATION SCHEMA TABLES  WHERE TABLE TYPE    BASE TABLE   Or  SELECT TABLE NAME  FROM INFORMATION SCHEMA TABLES  WHERE TABLE TYPE    BASE TABLE       AND TABLE CATALOG  dbName  -- for MySql  use  TABLE SCHEMA  dbName     PS  For SQL Server 2000  SELECT   FROM sysobjects WHERE xtype  U

User · Answer

SELECT   FROM information schema tables where TABLE TYPE    BASE TABLE    SQL Server 2012

User · Answer

select   from sysobjects where xtype  U

User · Answer

SQL Server 2000  2005  2008  2012  2014  2016  2017 or 2019  SELECT   FROM INFORMATION SCHEMA TABLES WHERE TABLE TYPE  BASE TABLE   To show only tables from a particular database SELECT TABLE NAME  FROM   lt DATABASE NAME gt   INFORMATION SCHEMA TABLES  WHERE TABLE TYPE    BASE TABLE   Or  SELECT TABLE NAME  FROM INFORMATION SCHEMA TABLES  WHERE TABLE TYPE    BASE TABLE       AND TABLE CATALOG  dbName  -- for MySql  use  TABLE SCHEMA  dbName     PS  For SQL Server 2000  SELECT   FROM sysobjects WHERE xtype  U

User · Answer

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   lt  gt   sysdiagrams  ORDER BY  TABLE SCHEMA    TABLE NAME    Results    MyDatabase dbo MyTable1  MyDatabase dbo MyTable2 MyDatabase MySchema MyTable3  MyDatabase MySchema MyTable4 etc

User · Answer

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

User · Answer

SELECT   FROM INFORMATION SCHEMA TABLES    OR  SELECT   FROM Sys Tables

User · Answer

exec sp msforeachtable  print

User · Answer

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  amp  2   0

User · Answer

SELECT sobjects name FROM sysobjects sobjects WHERE sobjects xtype    U

User · Answer

SELECT sobjects name FROM sysobjects sobjects WHERE sobjects xtype    U

User · Answer

--for oracle select tablespace name  table name from all tables      This link can provide much more information on this topic

User · Answer

exec sp msforeachtable  print

User · Answer

SELECT sobjects name FROM sysobjects sobjects WHERE sobjects xtype    U    Here is a list of other object types you can search for as well    AF  Aggregate function  CLR  C  CHECK constraint D  Default or DEFAULT constraint F  FOREIGN KEY constraint L  Log FN  Scalar function FS  Assembly  CLR  scalar-function FT  Assembly  CLR  table-valued function IF  In-lined table-function IT  Internal table P  Stored procedure PC  Assembly  CLR  stored-procedure PK  PRIMARY KEY constraint  type is K  RF  Replication filter stored procedure S  System table SN  Synonym SQ  Service queue TA  Assembly  CLR  DML trigger TF  Table function TR  SQL DML Trigger TT  Table type U  User table UQ  UNIQUE constraint  type is K  V  View X  Extended stored procedure

User · Answer

Using SELECT   FROM INFORMATION SCHEMA COLUMNS also shows you all tables and related columns

User · Answer

SELECT name  FROM sysobjects  WHERE xtype  U   ORDER BY name     SQL Server 2000 standard  still supported in SQL Server 2005

User · Answer

SELECT name  FROM sysobjects  WHERE xtype  U   ORDER BY name     SQL Server 2000 standard  still supported in SQL Server 2005

User · Answer

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  amp  2   0

User · Answer

SELECT sobjects name FROM sysobjects sobjects WHERE sobjects xtype    U    Here is a list of other object types you can search for as well    AF  Aggregate function  CLR  C  CHECK constraint D  Default or DEFAULT constraint F  FOREIGN KEY constraint L  Log FN  Scalar function FS  Assembly  CLR  scalar-function FT  Assembly  CLR  table-valued function IF  In-lined table-function IT  Internal table P  Stored procedure PC  Assembly  CLR  stored-procedure PK  PRIMARY KEY constraint  type is K  RF  Replication filter stored procedure S  System table SN  Synonym SQ  Service queue TA  Assembly  CLR  DML trigger TF  Table function TR  SQL DML Trigger TT  Table type U  User table UQ  UNIQUE constraint  type is K  V  View X  Extended stored procedure

User · Answer

exec sp msforeachtable  print

User · Answer

--for oracle select tablespace name  table name from all tables      This link can provide much more information on this topic

User · Answer

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

User · Answer

SELECT sobjects name FROM sysobjects sobjects WHERE sobjects xtype    U

User · Answer

SELECT name  FROM sysobjects  WHERE xtype  U   ORDER BY name     SQL Server 2000 standard  still supported in SQL Server 2005

User · Answer

SELECT   FROM information schema tables where TABLE TYPE    BASE TABLE    SQL Server 2012

[sql-server] How do I get list of all tables in a database using TSQL?

Examples related to sql-server

Examples related to tsql

Examples related to database-table