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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

another way  will also work on MySQL and PostgreSQL  select TABLE NAME from INFORMATION SCHEMA TABLES where TABLE TYPE    BASE TABLE

[sql-server-2008] Query to get the names of all tables in SQL Server 2008 Database

Examples related to sql-server-2008