This tutorial will show you the best way to get the names of all of the tables in a specific database on SQL Server or My Sql

For SQL Server:

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

For MySQL:

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

or you can use the following query to select all of the tables in the database named DbName

USE DbName
SELECT *
FROM sys.Tables

Update for the latest version of MSSQL Server 17.7

USE DbName
SELECT name FROM sys.Tables WHERE type_desc = 'USER_TABLE'