Apply mysql query to each table in a database
5
is there a way to apply a query to each table in a mysql database?
Something like
SELECT count(*) FROM {ALL TABLES}
-- gives the number of count(*) in each Table
and
DELETE FROM {ALL TABLES}
-- Like DELETE FROM TABLE applied on each Table
mysql
edited Dec 30 '18 at 4:50
Rick James
66.6k55899
asked Dec 6 '11 at 11:03
scravyscravy
5,7411151104
add a comment |
5
is there a way to apply a query to each table in a mysql database?
Something like
SELECT count(*) FROM {ALL TABLES}
-- gives the number of count(*) in each Table
and
DELETE FROM {ALL TABLES}
-- Like DELETE FROM TABLE applied on each Table
mysql
edited Dec 30 '18 at 4:50
Rick James
66.6k55899
asked Dec 6 '11 at 11:03
scravyscravy
5,7411151104
add a comment |
5
5
5
is there a way to apply a query to each table in a mysql database?
Something like
SELECT count(*) FROM {ALL TABLES}
-- gives the number of count(*) in each Table
and
DELETE FROM {ALL TABLES}
-- Like DELETE FROM TABLE applied on each Table
mysql
edited Dec 30 '18 at 4:50
Rick James
66.6k55899
asked Dec 6 '11 at 11:03
scravyscravy
5,7411151104
is there a way to apply a query to each table in a mysql database?
Something like
SELECT count(*) FROM {ALL TABLES}
-- gives the number of count(*) in each Table
and
DELETE FROM {ALL TABLES}
-- Like DELETE FROM TABLE applied on each Table
mysql
mysql
edited Dec 30 '18 at 4:50
Rick James
66.6k55899
asked Dec 6 '11 at 11:03
scravyscravy
5,7411151104
edited Dec 30 '18 at 4:50
Rick James
66.6k55899
asked Dec 6 '11 at 11:03
scravyscravy
5,7411151104
edited Dec 30 '18 at 4:50
Rick James
66.6k55899
edited Dec 30 '18 at 4:50
Rick James
66.6k55899
edited Dec 30 '18 at 4:50
Rick James
66.6k55899
66.6k55899
asked Dec 6 '11 at 11:03
scravyscravy
5,7411151104
asked Dec 6 '11 at 11:03
scravyscravy
5,7411151104
asked Dec 6 '11 at 11:03
scravyscravy
5,7411151104
5,7411151104
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
11
select sum(table_rows) as total_rows
from information_schema.tables
where table_schema = 'your_db_name'
beware this is just an approximate value
In order to delete the contents of all your tables you can do something like this
select concat('truncate ',table_name,';')
from information_schema.tables
where table_schema = 'your_db_name'
Then run the output of this query.
UPDATE.
This is a stored procedure to apply truncate table to all tables in a specific database
delimiter //
drop procedure if exists delete_contents //
create procedure delete_contents (in db_name varchar(100))
begin
declare finish int default 0;
declare tab varchar(100);
declare cur_tables cursor for select table_name from information_schema.tables where table_schema = db_name and table_type = 'base table';
declare continue handler for not found set finish = 1;
open cur_tables;
my_loop:loop
fetch cur_tables into tab;
if finish = 1 then
leave my_loop;
end if;
set @str = concat('truncate ', tab);
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end loop;
close cur_tables;
end; //
delimiter ;
call delete_contents('your_db_name');
answered Dec 6 '11 at 11:06
Nicola CossuNicola Cossu
36.9k127791
1
ah.. thanks. The prepare stmt from @str is very useful.
– scravy
Dec 15 '11 at 8:56
Your updated procedure is very useful. It looks likedb_namedoesn't affect anything though with the current setup (I removed it entirely without issue in the procedure).
– DACrosby
Oct 11 '13 at 8:53
Nice. To show individual count of entries in each table: <code>select table_name,table_rows from information_schema.tables where table_schema = 'your_db_name'</code>
– gaoithe
Mar 25 '15 at 9:47
add a comment |
0
if tables are related by any field you can use alias of tables like
select count(*) from table1 tb1, table2 tb2, table3 tb3 where
tb1.field1 = tb2.field2 and tb2.field2 = tb3.field3
similary,
delete from table1 tb1, table2 tb2, table3 tb3 where
tb1.field1 = tb2.field2 and tb2.field2 = tb3.field3
you can include conditions as per you requirements.
If tables have no relation then use below
SELECT
(SELECT COUNT(*) FROM table1 WHERE someCondition) as count1,
(SELECT COUNT(*) FROM table2 WHERE someCondition) as count2,
(SELECT COUNT(*) FROM table3 WHERE someCondition) as count3
you can remove where clause if there is no conditions.
OUTPUT:
|count1 | count2 | count3|
|50 |36 |21 |
answered Dec 6 '11 at 11:08
dku.rajkumardku.rajkumar
15k53156
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f8398847%2fapply-mysql-query-to-each-table-in-a-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
11
select sum(table_rows) as total_rows
from information_schema.tables
where table_schema = 'your_db_name'
beware this is just an approximate value
In order to delete the contents of all your tables you can do something like this
select concat('truncate ',table_name,';')
from information_schema.tables
where table_schema = 'your_db_name'
Then run the output of this query.
UPDATE.
This is a stored procedure to apply truncate table to all tables in a specific database
delimiter //
drop procedure if exists delete_contents //
create procedure delete_contents (in db_name varchar(100))
begin
declare finish int default 0;
declare tab varchar(100);
declare cur_tables cursor for select table_name from information_schema.tables where table_schema = db_name and table_type = 'base table';
declare continue handler for not found set finish = 1;
open cur_tables;
my_loop:loop
fetch cur_tables into tab;
if finish = 1 then
leave my_loop;
end if;
set @str = concat('truncate ', tab);
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end loop;
close cur_tables;
end; //
delimiter ;
call delete_contents('your_db_name');
answered Dec 6 '11 at 11:06
Nicola CossuNicola Cossu
36.9k127791
1
ah.. thanks. The prepare stmt from @str is very useful.
– scravy
Dec 15 '11 at 8:56
Your updated procedure is very useful. It looks likedb_namedoesn't affect anything though with the current setup (I removed it entirely without issue in the procedure).
– DACrosby
Oct 11 '13 at 8:53
Nice. To show individual count of entries in each table: <code>select table_name,table_rows from information_schema.tables where table_schema = 'your_db_name'</code>
– gaoithe
Mar 25 '15 at 9:47
add a comment |
11
select sum(table_rows) as total_rows
from information_schema.tables
where table_schema = 'your_db_name'
beware this is just an approximate value
In order to delete the contents of all your tables you can do something like this
select concat('truncate ',table_name,';')
from information_schema.tables
where table_schema = 'your_db_name'
Then run the output of this query.
UPDATE.
This is a stored procedure to apply truncate table to all tables in a specific database
delimiter //
drop procedure if exists delete_contents //
create procedure delete_contents (in db_name varchar(100))
begin
declare finish int default 0;
declare tab varchar(100);
declare cur_tables cursor for select table_name from information_schema.tables where table_schema = db_name and table_type = 'base table';
declare continue handler for not found set finish = 1;
open cur_tables;
my_loop:loop
fetch cur_tables into tab;
if finish = 1 then
leave my_loop;
end if;
set @str = concat('truncate ', tab);
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end loop;
close cur_tables;
end; //
delimiter ;
call delete_contents('your_db_name');
answered Dec 6 '11 at 11:06
Nicola CossuNicola Cossu
36.9k127791
1
ah.. thanks. The prepare stmt from @str is very useful.
– scravy
Dec 15 '11 at 8:56
Your updated procedure is very useful. It looks likedb_namedoesn't affect anything though with the current setup (I removed it entirely without issue in the procedure).
– DACrosby
Oct 11 '13 at 8:53
Nice. To show individual count of entries in each table: <code>select table_name,table_rows from information_schema.tables where table_schema = 'your_db_name'</code>
– gaoithe
Mar 25 '15 at 9:47
add a comment |
11
11
11
select sum(table_rows) as total_rows
from information_schema.tables
where table_schema = 'your_db_name'
beware this is just an approximate value
In order to delete the contents of all your tables you can do something like this
select concat('truncate ',table_name,';')
from information_schema.tables
where table_schema = 'your_db_name'
Then run the output of this query.
UPDATE.
This is a stored procedure to apply truncate table to all tables in a specific database
delimiter //
drop procedure if exists delete_contents //
create procedure delete_contents (in db_name varchar(100))
begin
declare finish int default 0;
declare tab varchar(100);
declare cur_tables cursor for select table_name from information_schema.tables where table_schema = db_name and table_type = 'base table';
declare continue handler for not found set finish = 1;
open cur_tables;
my_loop:loop
fetch cur_tables into tab;
if finish = 1 then
leave my_loop;
end if;
set @str = concat('truncate ', tab);
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end loop;
close cur_tables;
end; //
delimiter ;
call delete_contents('your_db_name');
answered Dec 6 '11 at 11:06
Nicola CossuNicola Cossu
36.9k127791
select sum(table_rows) as total_rows
from information_schema.tables
where table_schema = 'your_db_name'
beware this is just an approximate value
In order to delete the contents of all your tables you can do something like this
select concat('truncate ',table_name,';')
from information_schema.tables
where table_schema = 'your_db_name'
Then run the output of this query.
UPDATE.
This is a stored procedure to apply truncate table to all tables in a specific database
delimiter //
drop procedure if exists delete_contents //
create procedure delete_contents (in db_name varchar(100))
begin
declare finish int default 0;
declare tab varchar(100);
declare cur_tables cursor for select table_name from information_schema.tables where table_schema = db_name and table_type = 'base table';
declare continue handler for not found set finish = 1;
open cur_tables;
my_loop:loop
fetch cur_tables into tab;
if finish = 1 then
leave my_loop;
end if;
set @str = concat('truncate ', tab);
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end loop;
close cur_tables;
end; //
delimiter ;
call delete_contents('your_db_name');
answered Dec 6 '11 at 11:06
Nicola CossuNicola Cossu
36.9k127791
edited Dec 6 '11 at 11:26
answered Dec 6 '11 at 11:06
Nicola CossuNicola Cossu
36.9k127791
answered Dec 6 '11 at 11:06
Nicola CossuNicola Cossu
36.9k127791
answered Dec 6 '11 at 11:06
Nicola CossuNicola Cossu
36.9k127791
36.9k127791
1
ah.. thanks. The prepare stmt from @str is very useful.
– scravy
Dec 15 '11 at 8:56
Your updated procedure is very useful. It looks likedb_namedoesn't affect anything though with the current setup (I removed it entirely without issue in the procedure).
– DACrosby
Oct 11 '13 at 8:53
Nice. To show individual count of entries in each table: <code>select table_name,table_rows from information_schema.tables where table_schema = 'your_db_name'</code>
– gaoithe
Mar 25 '15 at 9:47
add a comment |
1
ah.. thanks. The prepare stmt from @str is very useful.
– scravy
Dec 15 '11 at 8:56
Your updated procedure is very useful. It looks likedb_namedoesn't affect anything though with the current setup (I removed it entirely without issue in the procedure).
– DACrosby
Oct 11 '13 at 8:53
Nice. To show individual count of entries in each table: <code>select table_name,table_rows from information_schema.tables where table_schema = 'your_db_name'</code>
– gaoithe
Mar 25 '15 at 9:47
1
1
ah.. thanks. The prepare stmt from @str is very useful.
– scravy
Dec 15 '11 at 8:56
ah.. thanks. The prepare stmt from @str is very useful.
– scravy
Dec 15 '11 at 8:56
Your updated procedure is very useful. It looks like
db_name doesn't affect anything though with the current setup (I removed it entirely without issue in the procedure).– DACrosby
Oct 11 '13 at 8:53
Your updated procedure is very useful. It looks like
db_name doesn't affect anything though with the current setup (I removed it entirely without issue in the procedure).– DACrosby
Oct 11 '13 at 8:53
Nice. To show individual count of entries in each table: <code>select table_name,table_rows from information_schema.tables where table_schema = 'your_db_name'</code>
– gaoithe
Mar 25 '15 at 9:47
Nice. To show individual count of entries in each table: <code>select table_name,table_rows from information_schema.tables where table_schema = 'your_db_name'</code>
– gaoithe
Mar 25 '15 at 9:47
add a comment |
0
if tables are related by any field you can use alias of tables like
select count(*) from table1 tb1, table2 tb2, table3 tb3 where
tb1.field1 = tb2.field2 and tb2.field2 = tb3.field3
similary,
delete from table1 tb1, table2 tb2, table3 tb3 where
tb1.field1 = tb2.field2 and tb2.field2 = tb3.field3
you can include conditions as per you requirements.
If tables have no relation then use below
SELECT
(SELECT COUNT(*) FROM table1 WHERE someCondition) as count1,
(SELECT COUNT(*) FROM table2 WHERE someCondition) as count2,
(SELECT COUNT(*) FROM table3 WHERE someCondition) as count3
you can remove where clause if there is no conditions.
OUTPUT:
|count1 | count2 | count3|
|50 |36 |21 |
answered Dec 6 '11 at 11:08
dku.rajkumardku.rajkumar
15k53156
add a comment |
0
if tables are related by any field you can use alias of tables like
select count(*) from table1 tb1, table2 tb2, table3 tb3 where
tb1.field1 = tb2.field2 and tb2.field2 = tb3.field3
similary,
delete from table1 tb1, table2 tb2, table3 tb3 where
tb1.field1 = tb2.field2 and tb2.field2 = tb3.field3
you can include conditions as per you requirements.
If tables have no relation then use below
SELECT
(SELECT COUNT(*) FROM table1 WHERE someCondition) as count1,
(SELECT COUNT(*) FROM table2 WHERE someCondition) as count2,
(SELECT COUNT(*) FROM table3 WHERE someCondition) as count3
you can remove where clause if there is no conditions.
OUTPUT:
|count1 | count2 | count3|
|50 |36 |21 |
answered Dec 6 '11 at 11:08
dku.rajkumardku.rajkumar
15k53156
add a comment |
0
0
0
if tables are related by any field you can use alias of tables like
select count(*) from table1 tb1, table2 tb2, table3 tb3 where
tb1.field1 = tb2.field2 and tb2.field2 = tb3.field3
similary,
delete from table1 tb1, table2 tb2, table3 tb3 where
tb1.field1 = tb2.field2 and tb2.field2 = tb3.field3
you can include conditions as per you requirements.
If tables have no relation then use below
SELECT
(SELECT COUNT(*) FROM table1 WHERE someCondition) as count1,
(SELECT COUNT(*) FROM table2 WHERE someCondition) as count2,
(SELECT COUNT(*) FROM table3 WHERE someCondition) as count3
you can remove where clause if there is no conditions.
OUTPUT:
|count1 | count2 | count3|
|50 |36 |21 |
answered Dec 6 '11 at 11:08
dku.rajkumardku.rajkumar
15k53156
if tables are related by any field you can use alias of tables like
select count(*) from table1 tb1, table2 tb2, table3 tb3 where
tb1.field1 = tb2.field2 and tb2.field2 = tb3.field3
similary,
delete from table1 tb1, table2 tb2, table3 tb3 where
tb1.field1 = tb2.field2 and tb2.field2 = tb3.field3
you can include conditions as per you requirements.
If tables have no relation then use below
SELECT
(SELECT COUNT(*) FROM table1 WHERE someCondition) as count1,
(SELECT COUNT(*) FROM table2 WHERE someCondition) as count2,
(SELECT COUNT(*) FROM table3 WHERE someCondition) as count3
you can remove where clause if there is no conditions.
OUTPUT:
|count1 | count2 | count3|
|50 |36 |21 |
answered Dec 6 '11 at 11:08
dku.rajkumardku.rajkumar
15k53156
edited Dec 6 '11 at 11:16
answered Dec 6 '11 at 11:08
dku.rajkumardku.rajkumar
15k53156
answered Dec 6 '11 at 11:08
dku.rajkumardku.rajkumar
15k53156
answered Dec 6 '11 at 11:08
dku.rajkumardku.rajkumar
15k53156
15k53156
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f8398847%2fapply-mysql-query-to-each-table-in-a-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
