Get All Table Names of a Particular Database by SQL Query

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

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 query for the table names list of a specific database?

Query for My sql:

show tables;

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.

Getting table names of all tables in a particular database in MS SQL Server using Java

You might want to run a simple select query on the database like this:

select TABLE_NAME from INFORMATION_SCHEMA.TABLES;

You can further filter out the table_names using where clauses like these:

SELECT 
    TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_CATALOG = ? AND TABLE_SCHEMA = ?;

---

Related Topics