Home MYSQL 8.0 – Get Table Columns from INFORMATION_SCHEMA.COLUMNS retuns other unrelated columns

Questions

MYSQL 8.0 – Get Table Columns from INFORMATION_SCHEMA.COLUMNS retuns other unrelated columns

April 4, 2023

I have built a stored procedure to return the list of Columns for a given Table and Database in JSON format.
However, it also returns columns from other tables despite the WHERE clause – This makes no sense, and I am unsure what I am doing wrong. Your help is most appreciated.

USE `db-xyz`;

DROP PROCEDURE IF EXISTS GetColumnsInfo;

DELIMITER $$

CREATE
DEFINER = 'admin'@'localhost'
PROCEDURE GetColumnsInfo (IN table_name varchar(128),OUT columns_info json)
BEGIN

  SELECT
    JSON_OBJECTAGG(
    COLUMN_NAME,
    JSON_OBJECT(
    "column_name", COLUMN_NAME,
    "data_type", DATA_TYPE
    )
    ) AS columns_info
  INTO @columns_info
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = table_name;

  SET columns_info = @columns_info;
END
$$

DELIMITER ;

This is how I am testing it:

USE `db-xyz`;

SET @table_name = 'Client';
SET @columns_info = '';

CALL GetColumnsInfo(@table_name, @columns_info);


-- Display the output
SELECT @columns_info AS columns_info;

>Solution :

You should name your procedure parameters something different from your column names. Column names are case-insensitive.

Right now your procedure has a query with this condition:

... AND TABLE_NAME = table_name;

Both of these operands reference the column of the table, not the procedure parameter. MySQL cannot tell that you meant the latter operand to be the procedure parameter.

So this evaluates as true because comparing a column to itself is bound to be true (unless the column is NULL).