Menu
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!
671
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.
I usually write queries in MS Access and did not know I had to make an alias on the subquery so this is very helpful. IF EXISTS can be used for check if a table is empty. IF EXISTS (select * from YourTable) SELECT 'Table is not empty' ELSE SELECT 'Table is empty'
I happen to like this method because it is standard SQL. @Stoleg Because the requirement was to select 1 or 0, and select 1 would return no rows if the table is empty. @mustaccio my apologies! It does! I wanted to write about EXISTS, but Sebastian Meine got it faster. Although I would prefer using EXISTS, there is one more method.
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. Second: Perform simple count action on primary key / main table on sql and count instances:
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
179
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
