Oi Pessoal ....
Bem ... Estou num mato sem cachorro .....
Preciso montar um SQL que me retorne a quantidade de objetos, separado por
tipo e por schema.
Eu consigo fazer os benditos selects separadamente mas preciso disso tudo em
uma unica linha para melhor visuazizar os resultados.
Sendo assim o que eu preciso é algo do tipo:
SCHEMA TABELAS VIEW SEQUENCES ....
SCHEMA TABELAS VIEW SEQUENCES ....
SCHEMA TABELAS VIEW SEQUENCES ....
SCHEMA TABELAS VIEW SEQUENCES ....
Segue abaixo os selects que montei separadamente ... (Agora só falta juntar
tudo !!!!)
SELECT specific_schema as "Schema", count(data_type) AS "Funções"
FROM information_schema.routines
WHERE specific_schema NOT IN
('dbateste','information_schema','pg_catalog','pg_temp_1',
'pg_toast','xmg','postgres','publico','public')
AND data_type <> '"trigger"'
GROUP BY specific_schema;
SELECT sequence_schema as "Schema", count(data_type) as "Sequence"
FROM information_schema.sequences
WHERE sequence_schema NOT IN
('dbateste','information_schema','pg_catalog','pg_temp_1',
'pg_toast','xmg','postgres','publico','public')
GROUP BY sequence_schema;
SELECT table_schema as "Schema", count(table_type) as "Tabelas"
FROM information_schema.tables
WHERE table_schema NOT IN
('dbateste','information_schema','pg_catalog','pg_temp_1',
'pg_toast','xmg','postgres','publico','public')
AND table_type = 'BASE TABLE'
GROUP BY table_schema;
SELECT specific_schema as "Schema", count(data_type) AS "Funções de
Triggers"
FROM information_schema.routines
WHERE specific_schema NOT IN
('dbateste','information_schema','pg_catalog','pg_temp_1',
'pg_toast','xmg','postgres','publico','public')
AND data_type = '"trigger"'
GROUP BY specific_schema;
SELECT table_schema as "Schema", count(table_type) as "Views"
FROM information_schema.tables
WHERE table_schema NOT IN
('dbateste','information_schema','pg_catalog','pg_temp_1',
'pg_toast','xmg','postgres','publico','public')
AND table_type = 'VIEW'
GROUP BY table_schema;
SELECT trigger_schema as "Schema", count(trigger_catalog) AS "Triggers"
FROM information_schema.triggers
WHERE trigger_schema NOT IN
('dbateste','information_schema','pg_catalog','pg_temp_1',
'pg_toast','xmg','postgres','publico','public')
GROUP BY trigger_schema;
--
Kenia Milene C. Galiego
DataBase Administrator
Oracle / PostgreSQL / MySql
Email: [EMAIL PROTECTED]
Blog: http://keniamilene.wordpress.com
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
