;
DROP TABLE IF EXISTS temp1,temp2;
CREATE TEMPORARY TABLE temp1
SELECT DISTINCT table_name FROM information_schema.columns a WHERE column_name IN
('category_l3_name',
'layout_category_l3_name','erp_category_l3_name')
and table_schema='bi'
;
CREATE TEMPORARY TABLE temp2
SELECT DISTINCT table_name FROM information_schema.columns b
WHERE column_name IN
(
'category_l2_append_key','layout_category_l2_append_key',
'layout_category_l2_append_name','category_l2_append_name'
)
and table_schema='bi'
;
SELECT table_name,column_name
,CONCAT('alter table ',table_name,' add category_l2_append_name varchar(100) AFTER ',column_name,';
','alter table ',table_name,' add category_l2_append_key varchar(30) AFTER ',column_name,';') AS sqltext
FROM
(
SELECT a.table_name,min(c.column_name) as column_name
FROM temp1 a JOIN information_schema.columns c
ON a.table_name=c.table_name AND c.column_name IN
('category_l3_name',
'layout_category_l3_name','erp_category_l3_name')
and c.table_schema='bi'
WHERE a.table_name NOT IN
(SELECT table_name FROM temp2 )
group by a.table_name
)
aa
ORDER BY table_name,column_name
;
DROP TABLE IF EXISTS temp1,temp2;
CREATE TEMPORARY TABLE temp1
SELECT DISTINCT table_name FROM information_schema.columns a WHERE column_name IN
('category_l3_name',
'layout_category_l3_name','erp_category_l3_name')
and table_schema='bi'
;
CREATE TEMPORARY TABLE temp2
SELECT DISTINCT table_name FROM information_schema.columns b
WHERE column_name IN
(
'category_l2_append_key','layout_category_l2_append_key',
'layout_category_l2_append_name','category_l2_append_name'
)
and table_schema='bi'
;
SELECT table_name,column_name
,CONCAT('alter table ',table_name,' add category_l2_append_name varchar(100) AFTER ',column_name,';
','alter table ',table_name,' add category_l2_append_key varchar(30) AFTER ',column_name,';') AS sqltext
FROM
(
SELECT a.table_name,min(c.column_name) as column_name
FROM temp1 a JOIN information_schema.columns c
ON a.table_name=c.table_name AND c.column_name IN
('category_l3_name',
'layout_category_l3_name','erp_category_l3_name')
and c.table_schema='bi'
WHERE a.table_name NOT IN
(SELECT table_name FROM temp2 )
group by a.table_name
)
aa
ORDER BY table_name,column_name
;