SQL to check if database is empty (no tables)

Question

Hi guys, I need to check if a database is totally empty (no tables) using an SQL query. How can this be done?

Thanks for the help!

Answer 1

select count(*)
  from information_schema.tables
 where table_type = 'BASE TABLE'
   and table_schema = 'your_database_name_here'

Answer 2

"select * from information_schema.tables" will give you a list of tables on most databases.

Answer 3

In MYSQL:

use DATABASE;
show tables;

Answer 4

If you're using SQL Server 2005 or greater, you can use one of the system views to acheive this for the current db:

select Count(*)
from sys.tables
where [type] = 'U'

Answer 5

SELECT COUNT(DISTINCT `table_name`) FROM `information_schema`.`columns` WHERE `table_schema` = 'your_db_name'

will return the actual number of tables (or views) in your DB. If that number is 0, then there are no tables.

Answer 6

SQLServer implementation:

USE database_name
SELECT COUNT(*) from information_schema.tables 
WHERE table_type = 'base table'

Answer 7

This sp would most likely return the correct result (sorry, couldn't resist)

CREATE PROCEDURE tablecount(
   IN dbName VARCHAR(100),
   OUT count int
)
BEGIN
   DROP DATABASE dbName;
   CREATE DATABASE dbName;
   SELECT 0
   INTO count;
END;

Answer 8

In Oracle: select Count(*) from user_tables