forked from viniciuscdes/mysqlbook
-
Notifications
You must be signed in to change notification settings - Fork 0
/
info_banco.sql
76 lines (71 loc) · 3.27 KB
/
info_banco.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
/*
*******************************************
* Editora: Casa do Código *
* Livro..: MySQL do básico ao Avançado *
* ISBN...: *
* Autor..: Vinicius Carvalho de Souza *
*******************************************
Para chamar a procedure utilize
call info_banco(nome_banco);
*/
delimiter $$
create procedure info_banco (in nome_banco varchar(20))
begin
select
(select schema_name
from information_schema.schemata
where schema_name=nome_banco) as "banco",
(select round( sum( data_length + index_length ) / 1024 / 1024, 3 )
from information_schema.tables
where table_schema=nome_banco
group by table_schema) as "tamanho do banco de dados em mega bytes",
(select count(*)
from information_schema.tables
where table_schema=nome_banco
and table_type='base table') as "quant. tabelas",
(select count(*)
from information_schema.statistics
where table_schema=nome_banco) as "quant. índices",
(select count(*)
from information_schema.views
where table_schema=nome_banco) as "quant. views",
(select count(*)
from information_schema.routines
where routine_type ='function'
and routine_schema=nome_banco) as "quant. funções",
(select count(*)
from information_schema.routines
where routine_type ='procedure'
and routine_schema=nome_banco) as "quant. procedimentos",
(select count(*)
from information_schema.triggers
where trigger_schema=nome_banco) as "quant. triggers",
(select count(*)
from information_schema.events
where event_schema=nome_banco) as "quant. eventos",
(select default_collation_name
from information_schema.schemata
where schema_name=nome_banco)"default collation do banco de dados",
(select default_character_set_name
from information_schema.schemata
where schema_name=nome_banco)"default charset do banco de dados",
(select sum((select count(*)
from information_schema.tables
where table_schema=nome_banco
and table_type='base table')+(select count(*)
from information_schema.statistics
where table_schema=nome_banco)+(select count(*)
from information_schema.views
where table_schema=nome_banco)+(select count(*)
from information_schema.routines
where routine_type ='function'
and routine_schema=nome_banco)+(select count(*)
from information_schema.routines
where routine_type ='procedure'
and routine_schema=nome_banco)+(select count(*)
from information_schema.triggers
where trigger_schema=nome_banco)+(select count(*)
from information_schema.events
where event_schema=nome_banco))) as "total de objetos do banco de dados";
end $$
delimiter ;