CODEWITHSUNDEEP
mysqlstored-procedures
kavita
kavita

Reputation: 845

MySQL stored procedure?

I am using mysql 5.0. I have a stored procedure which gets all the metadata of the database in a string and returns it. In it i call another stored procedure to get the metadata of the tables. I am using the following to get the list of schemas and their tables in a string

DECLARE tb_cur CURSOR FOR SELECT table_name, table_schema FROM INFORMATION_SCHEMA.TABLES;

To this i am attaching the fields metadata by calling another stored procedure:

call Getfields(tbNm, dbNm, fields);

fields has been declared as:

Declare fields blob default "";

I then concatenate all to get a single string:

set output = concat(output, dbNm, ".", tbNm, "*", fields);

dbNm and tbNm are declared as

varchar(50)

The output in my getFields stored proc is of type out fields blob

The output from my main procedure is OUT output blob

When I run the getfields separately it returns the field data for a table, but when i call it from within my main procedure it returns a 1406 error data too long for coltype on row 16!!! I saw that the type of column_type field in the columns table is long text so I have made the concatenation variable type blob.

Any hints/ suggestions ?? I have been stuck with this for a couple of days!!

-- query

SELECT GROUP_CONCAT(table_info SEPARATOR '~') table_info FROM (
  SELECT CONCAT(t.table_schema, '.', t.table_name, '*', 
  GROUP_CONCAT(CONCAT(c.column_name, '+', c.COLUMN_TYPE) SEPARATOR ','))
   table_info 
  FROM information_schema.TABLES t
    JOIN information_schema.COLUMNS c
    ON t.table_schema = c.table_schema AND t.table_name = c.table_name
   GROUP BY t.table_name
   ) t;

Upvotes: 0

Views: 573

Answers (2)

kavita
kavita

Reputation: 845

i think the result is curtailed due to group_concat_max_len. so i set it to the size of 4,294,967,295 (medium blob) and now it shows all the data.

Upvotes: 0

Devart
Devart

Reputation: 121902

More easy way, just run it -

SET @db = 'test'; -- Specify your schema

SELECT CONCAT(t.table_schema, '.', t.table_name, '*', GROUP_CONCAT(c.column_name)) table_info FROM information_schema.`TABLES` t
  JOIN information_schema.`COLUMNS` c
    ON t.table_schema = c.table_schema AND t.table_name = c.table_name
WHERE t.TABLE_SCHEMA = @db
GROUP BY t.table_name;

Edit

SELECT GROUP_CONCAT(table_info SEPARATOR '\r\n') table_info FROM (
  SELECT CONCAT(t.table_schema, '.', t.table_name, '*', GROUP_CONCAT(c.column_name)) table_info FROM information_schema.`TABLES` t
    JOIN information_schema.`COLUMNS` c
      ON t.table_schema = c.table_schema AND t.table_name = c.table_name
  WHERE t.TABLE_SCHEMA = @db
  GROUP BY t.table_name
  ) t;

Upvotes: 1

Related Questions