How to Get All Table Names from a Database

Get all table names of a particular database by SQL query?

Probably due to the way different sql dbms deal with schemas.

Try the following

For SQL Server:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='dbName'

For MySQL:

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='dbName' 

For Oracle I think the equivalent would be to use DBA_TABLES.

How do I get list of all tables in a database using TSQL?

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 [<DATABASE_NAME>].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' 

SQL Query to find all table names on a database with MySQL

LIKE is a good direction:

SELECT  table_name 
FROM  information_schema.TABLES 
WHERE  table_name LIKE  't#_contents#_s3%#_1#_2021' ESCAPE '#';

or if s3% part has always 4 characters then:

SELECT  table_name 
FROM  information_schema.TABLES 
WHERE  table_name LIKE  't#_contents#_s3__#_1#_2021' ESCAPE '#';

ESPACE allows to treat _ like _ and not a single character wildcard.

Display all the names of databases containing particular table

I got it done through following query:

SELECT name
FROM sys.databases
WHERE  CASE WHEN state_desc = 'ONLINE' 
         THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[heartbit]', 'U')
       END IS NOT NULL

Get all the table names that are available in all the schemas

Apologies, but i meant to say databases

A database and a schema, in SQL Server, are very different objects. A schema is an object within a database, where as a database is on "object" in an instance. The sys and INFORMATION_SCHEMA objects are also database objects and so will only list the objects within the database context they are referenced in.

For example the below 2 examples would both return details of tables in the MyDB database:

USE master;

SELECT *
FROM MyDB.sys.tables;
GO
USE MyDB;

SELECT *
FROM sys.tables;

If you want to get the details of tables in every Database you'll either need to query them separately, or generate and run a dynamic statement. The dynamic approach would be something like this:

USE master;
GO

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

DECLARE @Delimiter nvarchar(30) = @CRLF + N'UNION ALL' + @CRLF;

SET @SQL = (SELECT STRING_AGG(N'SELECT N' + QUOTENAME(d.[name],'''') + N' AS DatabaseName, t.[name] AS TableName FROM ' + QUOTENAME(d.[name]) + N'.sys.tables t', @Delimiter) WITHIN GROUP (ORDER BY d.database_id)
            FROM sys.databases d
            WHERE d.database_id > 4) + N';' --skip system databases

--PRINT @SQL; --Your best friend
EXEC sys.sp_executesql @SQL;

If you're using an older version of SQL Server, you'll need to use the "old" FOR XML PATH method, rather than STRING_AGG.

How to get all table names from a database?

You need to iterate over your ResultSet calling next().

This is an example from java2s.com:

DatabaseMetaData md = conn.getMetaData();
ResultSet rs = md.getTables(null, null, "%", null);
while (rs.next()) {
  System.out.println(rs.getString(3));
}

Column 3 is the TABLE_NAME (see documentation of DatabaseMetaData::getTables).

Search of table names

I'm using this and works fine

SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '%%'

---

Related Topics