dev/常用SQL语句/通用数据表/sys_table_attributes.sql

USE DCP;
/*生成表结构中数据列的定义*/
DROP TABLE `sys_table_attributes`;

CREATE TABLE `sys_table_attributes` AS (
SELECT
   CONCAT(information_schema.columns.TABLE_NAME,'-',information_schema.columns.COLUMN_NAME) AS  `id`,
  `information_schema`.`columns`.`TABLE_NAME`     AS  `table_attribute`,
  `information_schema`.`columns`.`COLUMN_NAME`    AS `column_attribute`,
  `information_schema`.`columns`.`COLUMN_COMMENT` AS `colunm_comment`,
  `information_schema`.`columns`.`DATA_TYPE`      AS `data_type`
FROM `information_schema`.`columns`
WHERE (`information_schema`.`columns`.`TABLE_SCHEMA` = 'dcp')
);

ALTER TABLE `dcp`.`sys_table_attributes`
  ADD  INDEX `idx_sys_table_attributes` (`table_attribute`);

ALTER TABLE `dcp`.`sys_table_attributes`
  ADD  INDEX `idx_sys_table_id` (`id`);

delete from `sys_table_attributes`;

insert into sys_table_attributes
SELECT
   CONCAT(information_schema.columns.TABLE_NAME,'-',information_schema.columns.COLUMN_NAME) AS  `id`,
  `information_schema`.`columns`.`TABLE_NAME`     AS  `table_attribute`,
  `information_schema`.`columns`.`COLUMN_NAME`    AS `column_attribute`,
  `information_schema`.`columns`.`COLUMN_COMMENT` AS `colunm_comment`,
  `information_schema`.`columns`.`DATA_TYPE`      AS `data_type`
FROM `information_schema`.`columns`
WHERE (`information_schema`.`columns`.`TABLE_SCHEMA` = 'dcp');