/* 
`information_schema`.`COLUMNS`
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
ORDINAL_POSITION
COLUMN_DEFAULT
IS_NULLABLE
DATA_TYPE
CHARACTER_MAXIMUM_LENGTH
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION
NUMERIC_SCALE
CHARACTER_SET_NAME
COLLATION_NAME
COLUMN_TYPE
COLUMN_KEY
EXTRA
PRIVILEGES
COLUMN_COMMENT
*/


-- SET [SESSION | GLOBAL] group_concat_max_len (default 1024)
SET SESSION group_concat_max_len = 10240;
SET @schema = 'ici_ma_base';
SET @table = 'ici_ma_table';
-- SELECT @schema, @table;

SELECT *
FROM `information_schema`.`COLUMNS`
WHERE TABLE_SCHEMA = 'nav_scot_sotranasa'
AND TABLE_NAME = 'attachement_pidi'
ORDER BY ORDINAL_POSITION ASC
;

-- SELECT *
SELECT CONCAT(
  'SELECT ',
  GROUP_CONCAT(COLUMN_NAME SEPARATOR ', '),
  ' FROM ',
  TABLE_NAME
  ) AS 'SELECT_ALL_FIELD_FROM_TABLE'
FROM `information_schema`.`COLUMNS`
WHERE TABLE_SCHEMA = @schema
AND TABLE_NAME = @table
ORDER BY ORDINAL_POSITION ASC
;

-- INSERT
-- REPLACE
SELECT CONCAT(
  'INSERT INTO ',
  TABLE_NAME,
  ' (',
  GROUP_CONCAT(COLUMN_NAME SEPARATOR ', '),
  ') VALUES (',
  GROUP_CONCAT(COLUMN_NAME SEPARATOR ', '),
  ')'
  ) AS 'INSERT_ALL_FIELD_FROM_TABLE'
FROM `information_schema`.`COLUMNS`
WHERE TABLE_SCHEMA = @schema
AND TABLE_NAME = @table
ORDER BY ORDINAL_POSITION ASC
;

-- UPDATE
SELECT CONCAT(
  'UPDATE ',
  TABLE_NAME,
  ' SET ',
  GROUP_CONCAT( CONCAT(COLUMN_NAME, ' = ', COLUMN_NAME) SEPARATOR ', '),
  ' WHERE 0'
  ) AS 'SELECT_ALL_FIELD_FROM_TABLE'
FROM `information_schema`.`COLUMNS`
WHERE TABLE_SCHEMA = @schema
AND TABLE_NAME = @table
ORDER BY ORDINAL_POSITION ASC
;