Thursday, 1 November 2012

MySQL, what are the values for this column in all tables?

If you have a complex schema, you may have an ID column that is used in several tables. Lets say you want to count all values of all such columns... without having to think about which tables it occurs in? Then this is the stored procedure for you:

DELIMITER $$

DROP   PROCEDURE IF EXISTS GetAllDistinctValuesByColumnName$$
CREATE PROCEDURE           GetAllDistinctValuesByColumnName (ColName VARCHAR(255))

BEGIN

DECLARE no_more_tables INT DEFAULT 0;
DECLARE my_table VARCHAR(255); 

DECLARE cur_tables CURSOR FOR
  SELECT DISTINCT
    TABLE_NAME
  FROM
    INFORMATION_SCHEMA.COLUMNS
  WHERE
    TABLE_SCHEMA = DATABASE()
  AND
    COLUMN_NAME = ColName;

DECLARE CONTINUE HANDLER FOR NOT FOUND
  SET no_more_tables = 1;

/* Result table */
CREATE TEMPORARY TABLE temp_my_values (
  my_table VARCHAR(255) NOT NULL,
  my_value VARCHAR(255) NOT NULL,
  my_count INT UNSIGNED NOT NULL
);


OPEN cur_tables; 

FETCH cur_tables INTO my_table; 

REPEAT

SET @ugly_sql := CONCAT('
  INSERT INTO temp_my_values
  SELECT "', my_table, '", ', ColName, ', COUNT(*)
  FROM ', my_table, '
  GROUP BY ', ColName
);

-- SELECT @ugly_sql;

PREPARE mySt FROM @ugly_sql;
EXECUTE mySt;

FETCH cur_tables INTO my_table;

UNTIL no_more_tables = 1 
END REPEAT; 

CLOSE cur_tables; 

SELECT * FROM temp_my_values; 
DROP TABLE temp_my_values; 

END$$

DELIMITER ;


Post a Comment