Bases de datos

MySQL

Consulta dinámica de cantidad de registros por tabla

Última actualización: 09-06-2018 16:45

Generación dinámica de SELECTs de conteo de registros de todas las tablas de la base de datos a la que se está conectado.

---

select XSQL from ( select @rownum := @rownum + 1 as rownum, concat( case when @rownum = 1 then '' else ' union ' end, 'select ''', table_name, ''' as TABLE_NAME, count(*) as NROWS from ', table_name) as XSQL from information_schema.tables a, (SELECT @rownum := 0) r where a.table_schema = database() and a.table_type = 'BASE TABLE' order by a.table_name ) x order by x.rownum;

select XSQL
from (
		select  @rownum := @rownum + 1 as rownum,
			concat(	case when @rownum  = 1 then '' else ' union ' end,
					'select ''', table_name, ''' as TABLE_NAME, count(*) as NROWS from ', table_name) as XSQL 
		from information_schema.tables a,   
				(SELECT @rownum := 0) r
		where a.table_schema = database() and a.table_type = 'BASE TABLE'
        order by a.table_name 
        ) x
order by x.rownum;