12

I would like to get all of the column names from a MySQL table, loop through each column name and then run a stored procedure using those column names as a variable. Something to the effect of:

colnames = get column names from table

for each colname
  if something changed then
    do something
  else
    do something else

It looks like SHOW COLUMNS FROM myTable will give me the column names, but how would I get the column names into a loop?

I would really like to run all of this in a stored procedure using native SQL. Since I'm still learning the intricacies of MySQL, and this would really help out my project. Thanks for your help.

RyanKDalton
  • 1,271
  • 3
  • 14
  • 30
  • I just want to write this as native SQL from within MySQL workbench. I was planning to use the column name as part of an UPDATE query. – RyanKDalton Feb 09 '11 at 20:50

2 Answers2

22

I think you want something like this:

DECLARE col_names CURSOR FOR
  SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tbl_name'
  ORDER BY ordinal_position;


select FOUND_ROWS() into num_rows;

SET i = 1;
the_loop: LOOP

   IF i > num_rows THEN
        CLOSE col_names;
        LEAVE the_loop;
    END IF;


    FETCH col_names 
    INTO col_name;     

     //do whatever else you need to do with the col name

    SET i = i + 1;  
END LOOP the_loop;
user470714
  • 2,858
  • 1
  • 28
  • 34
  • That looks like exactly what I am after. Thanks so much! The first SELECT statement works, however, I am getting hung up with a syntax error when I add "DECLARE col_names CURSOR FOR". Any thoughts as to why? – RyanKDalton Feb 09 '11 at 22:44
  • 5
    OK! Turns out I just needed to change the delimiter and declare the num_rows, i, col_name and it works great. Thanks so much! – RyanKDalton Feb 09 '11 at 23:15
  • 1
    And also needs to `open col_names;`. – keineahnung2345 Jan 13 '20 at 03:00
5

You can write a query against information_schema to get the column names:

SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tbl_name'
  ORDER BY ordinal_position

The column names are then returned just as any data from a table would be.

Ned Batchelder
  • 364,293
  • 75
  • 561
  • 662
  • 7
    That's an excellent start to give me the column names! How would I put those into a variable (list?) to iterate over those, though? – RyanKDalton Feb 09 '11 at 21:40