How to get example values from every column in a schema

Question

I'm reverse-engineering a MySQL database and I'd like to get a list of example values from every column in every table. I'd like to run a query like this:

select
  table_name,
  column_name,
  group_concat(
    (select distinct table_name.column_name limit 100)
    separator ','
  ) as examples
from
  information_schema.columns
where
  table_schema = 'myschema'
;

I'd like the output to look something like this:

table1 column1 (123,124,234)
table1 column2 ('Joe','Sara','Bob')

MySQL won't accept table_name.column_name as valid syntax. What's the right way to write this query?

Is [this](http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure) related? — rene, Jun 01 '13 at 08:54
That's related, but it's not obvious how to use a prepared statement to do this query. MySQL requires the parameter values to be user variables, so it seems like a solution would have to use a prepared statement which loops over the results from the information_schema.columns table. — Sam, Jun 01 '13 at 09:11

score 0 · Answer 1 · answered Jun 01 '13 at 08:56

0

I think Sam, you are looking for something like that, or at least it would be a better approach:

select
  table_name,
  column_name,
  group_concat((column_name) separator ',') as examples
from
  information_schema.columns
where
  table_schema = 'test'
GROUP BY table_name
;

answered Jun 01 '13 at 08:56

medina

8,051
4
25
24

I'm not trying to list the columns on each table, I'm trying to get a list of example values from each column. In the result set I'd like rows like "table1 column1 (123,124,234)" "table1 column2 ('Joe','Sara','Bob')". – Sam Jun 01 '13 at 09:08

score 0 · Answer 2 · answered Jun 01 '13 at 15:13

Based on rene's suggestion, I wrote a stored procedure which outputs examples of values from each column in every table. It's ugly and slow, but it works. I'd welcome suggestions on how to improve this code.

DELIMITER // 
CREATE PROCEDURE column_example_values(
    IN db_name VARCHAR(64), 
    IN tbl VARCHAR(64), 
    IN col VARCHAR(64), 
    OUT result MEDIUMTEXT)
BEGIN
    SET @s = CONCAT('SELECT GROUP_CONCAT(tbl1.',col,
                    ' separator \',\') FROM (SELECT DISTINCT ',
                    col,' FROM ',db_name,'.',tbl,
                    ' LIMIT 100) tbl1 INTO @result1');
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    SET result = IFNULL(@result1,'');
END;
//
DELIMITER ;

DELIMITER // 
CREATE PROCEDURE all_columns_example_values(IN db_name VARCHAR(64))
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE tbl, col VARCHAR(64);

  DECLARE cur1 CURSOR FOR 
    SELECT 
      table_name,
      column_name 
    FROM 
      information_schema.columns 
    WHERE 
      table_schema = db_name 
    ORDER BY 
      table_name, 
      column_name;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  CREATE TEMPORARY TABLE results (
    tbl VARCHAR(64), col VARCHAR(64), examples MEDIUMTEXT);

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO tbl, col;
    IF done THEN
      LEAVE read_loop;
    END IF;
    CALL column_example_values(db_name,tbl,col,@result);
    INSERT INTO results (tbl, col, examples) VALUES (tbl, col, @result);
  END LOOP;

  CLOSE cur1;

  SELECT * FROM results;
  DROP TABLE results;
END;
//
DELIMITER ;

It can be called with

CALL all_columns_example_values('mydb');

How to get example values from every column in a schema

2 Answers2