1

I'm not DBA or...But I am just very interested in writing query like to learn the trick of it..Now I'm asking myself this question:

How can I get n rows from information_Schema from each of databases? (Without knowing the number of databases)

For example ... We have 3 database ( (information_Schema,stack,overflow) and we don't know that.

I want to get the 20 tables from each of databases .. How can I do that?

One other things...please don't give me procedure.

Milad
  • 25
  • 5
  • I'm confused - do you want to list the tables, or list full rows from the tables? If full rows, it cannot be done without a stored procedure or application code because it requires a dynamic SQL string to be constructed. You can't join against information_schema by table name because tables are string values there, not identifiers. – Michael Berkowski Dec 13 '15 at 15:49
  • tnx ... look... yes i want to list tables but based on conditions.. simple example : select table_name from information_Schema.tables >> it's give us all tables from all databases. now i want query like that but according to this condition : select 20 tables from each of databases(without knowing how many database we have.) i want query to work every where not just for one database.. – Milad Dec 13 '15 at 18:09

1 Answers1

0

You can use the following to achieve what you want:

set @schema_name='';
set @num=0;

select table_schema, table_name FROM (
select 
  table_schema, 
  table_name, 
  @num := if(@schema_name = table_schema, @num + 1, 1) as dummy_1,
  @schema_name := table_schema as dummy_2,
  @num as row_number
  from information_schema.tables 
  group by table_schema, table_name
  having row_number <= 20) A ;
Richard St-Cyr
  • 970
  • 1
  • 8
  • 14