Closed bjverde closed 5 years ago
SELECT r.ROUTINE_SCHEMA as TABLE_SCHEMA
,r.SPECIFIC_NAME as TABLE_NAME
,r.ROUTINE_TYPE as TABLE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES as r
select p.SPECIFIC_SCHEMA as TABLE_SCHEMA
, p.SPECIFIC_NAME as TABLE_NAME
, count(*) as COLUMN_QTD
, 'PROCEDURE' as TABLE_TYPE
from information_schema.parameters as p
group by p.SPECIFIC_SCHEMA, p.SPECIFIC_NAME
https://dataedo.com/kb/query/mysql/list-stored-procedure-parameters
select vg.TABLE_SCHEMA
,vg.TABLE_NAME
,vg.COLUMN_QTD
,vg.TABLE_TYPE
from
(
select vt.TABLE_SCHEMA
,vt.TABLE_NAME
,count(*) as COLUMN_QTD
,vt.TABLE_TYPE
from
(
SELECT t.TABLE_SCHEMA
,t.TABLE_NAME
,case when upper(t.TABLE_TYPE) = 'BASE TABLE' then 'TABLE' else upper(t.TABLE_TYPE) end as TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES as t
,INFORMATION_SCHEMA.COLUMNS as c
WHERE t.TABLE_NAME = c.TABLE_NAME
and t.TABLE_SCHEMA = c.TABLE_SCHEMA
and (t.TABLE_TYPE = 'BASE TABLE' OR t.TABLE_TYPE = 'VIEW')
and t.TABLE_SCHEMA not in ('sys','performance_schema','mysql','information_schema')
) as vt
group by vt.TABLE_SCHEMA
,vt.TABLE_NAME
,vt.TABLE_TYPE
union
select vp.TABLE_SCHEMA
,vp.TABLE_NAME
,count(*) as COLUMN_QTD
,'PROCEDURE' as TABLE_TYPE
from
(
select p.SPECIFIC_SCHEMA as TABLE_SCHEMA
,p.SPECIFIC_NAME as TABLE_NAME
,p.routine_type as TABLE_TYPE
from information_schema.routines as r
left join information_schema.parameters as p
on p.specific_schema = r.routine_schema
and p.specific_name = r.specific_name
where r.routine_schema not in ('sys', 'information_schema','mysql', 'performance_schema')
and p.routine_type = 'PROCEDURE'
) as vp
group by vp.TABLE_SCHEMA
,vp.TABLE_NAME
,vp.TABLE_TYPE
) as vg
order by
vg.TABLE_SCHEMA
,vg.TABLE_TYPE
,vg.TABLE_NAME
https://github.com/bjverde/formDin/blob/master/base/classes/webform/TDAO.class.php#L1441