Reputation: 1486

SQL to check if database is empty (no tables)

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!

Upvotes: 11

Answers (12)

Alexandre Daubricourt

Reputation: 4903

For MySQL 8, I had to tweak my query into:

SELECT COUNT(*) from information_schema.tables 
WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA=DATABASE();

Upvotes: 0

Aviv

Reputation: 14467

Solution: In order to verify your databases is not empty you can watch list of tables and measure instances in it.

first: perform simple connection to your db mysql -u <userName> -p ,run show databases; pick your database using use <databaseName>; and then run show tables; and expect to have list of tables.

+----------------------------------------+
| Tables_in_databaseName                 |
+----------------------------------------+
| databaseA                              |
| databaseB                              |
| databaseC                              |
+----------------------------------------+

Second: Perform simple count action on primary key / main table on sql and count instances:

select count(*) from <primaryKeyColumn>;

count result should be > 0

+----------+
| count(*) |
+----------+
|   100    |
+----------+

Upvotes: 0

Eduardo Cuomo

Reputation: 18937

In bash:

db_name='my_db'
mysql -s --skip-column-names -e "SELECT COUNT(DISTINCT table_name) FROM information_schema.columns WHERE table_schema = '$db_name'"

Upvotes: 0

Christian Schäfer-Neth

Reputation: 61

To get a list of all databases without tables in MySQL:

use information_schema

select schema_name from `schemata` s
  left join `tables` t on s.schema_name = t.table_schema
  where t.table_name is null
;

Cheers, Christian

Upvotes: 6

JCotton

Reputation: 11760

I needed something that would give me an exit code to use in Bash. This builds off of @longneck's solid answer. If the database has tables, the select statement will set the contents column as "has tables". Grep will return a successful 0 in this case, otherwise it will return a non-zero.

#!/bin/bash
user=username
pw=passwd
db=database
mysql -u ${user} -p"${pw}" -D ${db} --execute="SELECT CASE COUNT(*) WHEN '0' THEN 'empty database' ELSE 'has tables' END AS contents FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = '${db}';" | grep 'has tables'
echo $?

Upvotes: 0

MiffTheFox

Reputation: 21565

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.

Upvotes: 16

Keith

Reputation: 966

In Oracle: select Count(*) from user_tables

Upvotes: 0

longneck

Reputation: 12226

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

Upvotes: 14

Barry Gallagher

Reputation: 6246

SQLServer implementation:

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

Upvotes: 2

James Alexander

Reputation: 6302

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'

Upvotes: 2

Fernando Briano

Reputation: 7757

In MYSQL:

use DATABASE;
show tables;

Upvotes: 7

David

Reputation: 2174

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

Upvotes: 0

SQL to check if database is empty (no tables)

Answers (12)

Related Questions