MYSQL: create table class sql

create-table-class.sql

---Created by Gumpanat Keardkeawfa on 2017Mar01.---
---Copyright © 2017 Auycro. All rights reserved.---
---Ref:
---  http://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql
---  http://stackoverflow.com/questions/5873170/generate-class-from-database-table

SELECT 
  REPLACE(table_list.class_template,'{COLUMN_NAME}',tmp3.columns)
FROM
( 
  SELECT
    CONCAT('\\\\',table_schema,'.',table_name,'\n',
    'Public class tbl_',table_name,'\n{\n',
    '\t{COLUMN_NAME}',
    '\n}') AS class_template,table_name as tb_name 
  FROM information_schema.tables
  WHERE table_schema = 'dummy_00004'
) as table_list,
(
  SELECT 
    tmp2.table_name,
    GROUP_CONCAT(tmp2.columns SEPARATOR '\n\t') as columns
    FROM
    (
    SELECT 
      CONCAT('public ', tmp1.col_data_type,' ',column_name,' { get;set; }') as columns,
      table_name
    FROM
    (
      SELECT 
        case data_type
          when 'bigint' then 'long'
          when 'binary' then 'byte[]'
          when 'bit' then 'bool'
          when 'char' then 'string'
          when 'date' then 'DateTime'
          when 'datetime' then 'DateTime'
          when 'datetime2' then 'DateTime'
          when 'datetimeoffset' then 'DateTimeOffset'
          when 'decimal' then 'decimal'
          when 'float' then 'float'
          when 'image' then 'byte[]'
          when 'int' then 'int'
          when 'money' then 'decimal'
          when 'nchar' then 'string'
          when 'ntext' then 'string'
          when 'numeric' then 'decimal'
          when 'nvarchar' then 'string'
          when 'real' then 'double'
          when 'smalldatetime' then 'DateTime'
          when 'smallint' then 'short'
          when 'smallmoney' then 'decimal'
          when 'text' then 'string'
          when 'time' then 'TimeSpan'
          when 'timestamp' then 'DateTime'
          when 'tinyint' then 'byte'
          when 'uniqueidentifier' then 'Guid'
          when 'varbinary' then 'byte[]'
          when 'varchar' then 'string'
          when 'mediumtext' then 'string'
          else data_type
        end as col_data_type,
        column_name,
        table_name
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_SCHEMA = 'dummy_00004'
    ) as tmp1
  ) as tmp2
  GROUP BY tmp2.table_name
) as tmp3
WHERE table_list.tb_name = tmp3.table_name