DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `structure`(IN `db` VARCHAR(64) CHARSET utf8, IN `tbl` VARCHAR(64) CHARSET utf8, IN `dir` VARCHAR(256) CHARSET utf8)
BEGIN
	SET @cnt_num = 0;
	SET @cnt_tot = 0;
    
	/* create reporting table*/
    EXECUTE IMMEDIATE CONCAT
   	('CREATE DATABASE IF NOT EXISTS ',db,'_profile');
	EXECUTE IMMEDIATE CONCAT
    ('DROP TABLE IF EXISTS ',db,'_profile.',tbl,'_structure');
	EXECUTE IMMEDIATE CONCAT(
    'CREATE TABLE ',db,'_profile.',tbl,'_structure (
        Id BIGINT NOT NULL AUTO_INCREMENT, 
		Name VARCHAR(64) NULL,
        Format VARCHAR(64) NULL,
        Length VARCHAR(256) NULL,
        IsNull VARCHAR(3) NULL,
        Description VARCHAR(1024) NULL,
        PRIMARY KEY (Id)
    ) ENGINE = InnoDB');
    
	/*get total number of columns*/
    SELECT COUNT(*) INTO @cnt_tot FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=db AND TABLE_NAME=tbl;
	  
    WHILE @cnt_num < @cnt_tot DO
    	SET @cnt_num = @cnt_num+1;
        
        /* get column name abd data types*/
        SELECT 
        	COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_COMMENT 
       	INTO 
        	@column_name, @data_type, @is_nullable, @column_comment
        FROM information_schema.COLUMNS 
        WHERE TABLE_SCHEMA=db AND TABLE_NAME=tbl AND ORDINAL_POSITION = @cnt_num;

		/* get minimum and maximum length of data*/
       	EXECUTE IMMEDIATE 
        	CONCAT(
                'SELECT 
        			MIN(CHAR_LENGTH(',@column_name,')), 
                	MAX(CHAR_LENGTH(',@column_name,'))
                	INTO @lenmin, @lenmax
        		FROM ',db,'.',tbl
			);
            
    	/*write result into reporting table*/
        EXECUTE IMMEDIATE CONCAT(
        	'INSERT INTO ',db,'_profile.',tbl,'_structure (Name, Format, Length, IsNull, Description) 
            VALUES ( @column_name, @data_type, CONCAT(IF(@lenmin=@lenmax,\'\',CONCAT(@lenmin, \'-\')) , @lenmax), @is_nullable, @column_comment )');
       
    END WHILE;
    
	/*export reporting file*/    
    SET @file = CONCAT(REPLACE(dir,'\\','\\\\'),CONCAT(tbl,'_structure_',REPLACE(NOW(),':',''),'.csv'));
	
	EXECUTE IMMEDIATE 
    CONCAT('SELECT * INTO OUTFILE \'',@file,'\'
    FIELDS TERMINATED BY \';\'
    OPTIONALLY ENCLOSED BY \'\"\'
    LINES TERMINATED BY \'\\n\'
    FROM ',db,'_profile.',tbl,'_structure');
END$$
DELIMITER ;