SQL to check if database is empty (no tables)

Question

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!

Presumably, you mean 'no user defined tables or views' since the system catalog will exist. — Jonathan Leffler
– Jonathan Leffler, Commented Jun 18, 2009 at 14:37

MiffTheFox · Accepted Answer · 2009-06-27 23:44:26Z

17

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.

edited Jun 27, 2009 at 23:44

answered Jun 18, 2009 at 14:18

MiffTheFox

21.6k14 gold badges73 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

longneck Over a year ago

this will also return the number of views.

A-K Over a year ago

But if that number is more than 0? Maybe there are no tables, but there are views?

MiffTheFox Over a year ago

@AlexKuznetsov Yes, but I don't believe that you can have a view without a table to base it on?

A-K Over a year ago

@MiffTheFox: CREATE DATAbASE DbWithNoTablesAndOneView; GO USE DbWithNoTablesAndOneView; GO CREATE VIEW dbo.ViewWihtoutUnderlyingTable As SELECT 1 AS n; GO

A-K Over a year ago

A more realistic example: create view wrapped_rand_view as select rand( ) as random_value go create function wrapped_rand() returns float as begin declare @f float set @f = (select random_value from wrapped_rand_view) return @f end mydatabasesupport.com/forums/sqlserver-programming/…

|

longneck · Accepted Answer · 2009-06-18 14:31:12Z

14

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

edited Jun 18, 2009 at 14:31

answered Jun 18, 2009 at 14:16

longneck

12.3k3 gold badges39 silver badges49 bronze badges

2 Comments

EdanB Over a year ago

Hmm, I think there is something wrong with this SQL. I'm using MySQL and getting 0 on a database with tables in it.

Ismail Gunes Over a year ago

It worked well for me: But I hardly found how to call First I wrote result = cmd.ExecuteNonQuery which returns -1 Then after I found result = int.Parse(cmd.ExecuteScalar().ToString()); which returns zero if there is no table.

Fernando Briano · Accepted Answer · 2009-06-18 14:17:46Z

7

In MYSQL:

use DATABASE;
show tables;

answered Jun 18, 2009 at 14:17

Fernando Briano

7,77613 gold badges60 silver badges75 bronze badges

Comments

Christian Schäfer-Neth · Accepted Answer · 2013-09-03 08:32:48Z

6

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

answered Sep 3, 2013 at 8:32

Christian Schäfer-Neth

611 silver badge1 bronze badge

1 Comment

Charles Harmon Over a year ago

This is absolutely the right way to go if you want to get all empty databases. Thanks @Christian!

James Alexander · Accepted Answer · 2009-06-18 14:18:16Z

2

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'

answered Jun 18, 2009 at 14:18

James Alexander

6,40010 gold badges46 silver badges58 bronze badges

Comments

Barry Gallagher · Accepted Answer · 2009-06-18 14:20:37Z

2

SQLServer implementation:

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

answered Jun 18, 2009 at 14:20

Barry Gallagher

6,2464 gold badges29 silver badges30 bronze badges

Comments

David · Accepted Answer · 2009-06-18 14:17:23Z

0

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

answered Jun 18, 2009 at 14:17

David

2,17414 silver badges11 bronze badges

Comments

Keith · Accepted Answer · 2009-06-18 15:09:55Z

0

In Oracle: select Count(*) from user_tables

answered Jun 18, 2009 at 15:09

Keith

9866 silver badges15 bronze badges

Comments

JCotton · Accepted Answer · 2012-02-09 00:54:25Z

0

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 $?

answered Feb 9, 2012 at 0:54

JCotton

11.8k5 gold badges60 silver badges61 bronze badges

Comments

Eduardo Cuomo · Accepted Answer · 2016-08-10 20:32:12Z

0

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'"

answered Aug 10, 2016 at 20:32

Eduardo Cuomo

19.2k7 gold badges121 silver badges98 bronze badges

Comments

Aviv · Accepted Answer · 2019-11-19 08:43:09Z

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    |
+----------+

Alexandre Daubricourt · Accepted Answer · 2024-10-21 10:59:59Z

0

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();

answered Oct 21, 2024 at 10:59

Alexandre Daubricourt

5,4612 gold badges42 silver badges41 bronze badges

Collectives™ on Stack Overflow

SQL to check if database is empty (no tables)

12 Answers 12

6 Comments

2 Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

12 Answers 12

6 Comments

2 Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related