Tutorial :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!


Solution:1

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.


Solution:2

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


Solution:3

In MYSQL:

use DATABASE;  show tables;  


Solution:4

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


Solution:5

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'  


Solution:6

SQLServer implementation:

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


Solution:7

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


Solution:8

In Oracle: select Count(*) from user_tables


Solution:9

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


Solution:10

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

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »