information_schema unknown inside a procedure

Question

I successfully run a query selecting the column names of a table in the information_schema, however if i run the same query in the stored procedure to use it with a cursor, then the information_schema is unknown. Can anybody tell me the reason and also help me solve the issue? thanks Here is the query

 SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'database_name'
AND table_name='Table_name';

And here is the procedure.

    DELIMITER $$

    CREATE PROCEDURE build_column_names_str (INOUT column_list varchar(4000))
    BEGIN

     DECLARE v_finished INTEGER DEFAULT 0;
            DECLARE v_column varchar(100) DEFAULT "";

    -- declare cursor for column names
    DEClARE column_cursor CURSOR FOR 
     SELECT column_name
        FROM information_schema.columns
        WHERE table_schema = 'database_name'
        AND table_name='Table_name';

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET v_finished = 1;

     OPEN column_cursor;

     get_column: LOOP

     FETCH column_cursor INTO v_column;

     IF v_finished = 1 THEN 
     LEAVE get_column;
     END IF;

     -- build column name list
     SET column_list = CONCAT(v_column,",",column_list);

     END LOOP get_column;

     CLOSE column_cursor;

    END$$

    DELIMITER ;

    SET @column_list = "";
    CALL build_column_names_str(@column_list);
    SELECT @column_list;

Thanks

Answer 1

Your code is correct.

Check privileges on procedure execution
or replace a procedure with

SELECT group_concat(column_name)
        FROM information_schema.columns
        WHERE table_schema = 'database_name'
        AND table_name='Table_name';