(8.0.29)
drop procedure proc_makeER;
delimiter //
create procedure proc_makeER()
begin
-- ワーク変数
declare wk1 numeric;
declare wk2 numeric;
declare wk3 numeric;
declare wk4 numeric;
declare wk5 numeric;
declare wk6 numeric;
declare va1 varchar(4000);
declare va2 varchar(4000);
declare va3 varchar(4000);
declare va4 varchar(4000);
-- カーソル関連
declare done int;
declare cur10 cursor for select table_name from information_schema.tables
where table_schema='test'
and table_type = 'BASE TABLE'
order by rand() limit 2;
declare continue handler for sqlstate '02000' set done = 1;
-- テーブル名配列
drop temporary table if exists tabnames;
create temporary table if not exists tabnames(id int primary key auto_increment
,table_name varchar(4000)
);
-- 結合条件配列
drop temporary table if exists joincols;
create temporary table if not exists joincols(id int primary key auto_increment
,table_name1 varchar(4000)
,table_name2 varchar(4000)
,column_name1 varchar(30)
,column_name2 varchar(30)
);
-- ①テーブル名配列にテーブルを格納
set wk1 = 1;
set done = 0;
open cur10;
fetch cur10 into va1;
while done != 1 do
insert into tabnames(table_name) values(va1);
set wk1 = wk1+1;
fetch cur10 into va1;
end while;
close cur10;
set va1 := (select table_name from tabnames where id = 1);
set va2 := (select table_name from tabnames where id = 2);
-- ②結合条件配列の作成
set @q := '';
set @column_name1 :='';
set @column_name2 :='';
set @q := concat(@q, ' select t1.column_name , t2.column_name into @column_name1,@column_name2 ');
set @q := concat(@q, ' from information_schema.columns t1 ');
set @q := concat(@q, ' inner join information_schema.columns t2 ');
set @q := concat(@q, ' on t1.data_type = t2.data_type ');
set @q := concat(@q, ' and coalesce(t1.character_maximum_length,-1) = coalesce(t2.character_maximum_length,-1) ');
set @q := concat(@q, ' and coalesce(t1.numeric_precision,-1) = coalesce(t2.numeric_precision,-1) ');
set @q := concat(@q, ' and coalesce(t1.numeric_scale,-1) = coalesce(t2.numeric_scale,-1) ');
set @q := concat(@q, ' and t1.table_schema = ''test'' ');
set @q := concat(@q, ' and t1.table_name = ''',va1,''' ');
set @q := concat(@q, ' and t2.table_schema = ''test'' ');
set @q := concat(@q, ' and t2.table_name = ''',va2,''' ');
-- 主キーとユニークインデックスカラムは除外
set @q := concat(@q, ' and not exists ( select 1 ');
set @q := concat(@q, ' from information_schema.statistics ');
set @q := concat(@q, ' where NON_UNIQUE = 0 ');
set @q := concat(@q, ' and table_schema = t1.table_schema ');
set @q := concat(@q, ' and table_name = t1.table_name ');
set @q := concat(@q, ' and column_name = t1.column_name ) ');
set @q := concat(@q, ' and not exists ( select 1 ');
set @q := concat(@q, ' from information_schema.statistics ');
set @q := concat(@q, ' where NON_UNIQUE = 0 ');
set @q := concat(@q, ' and table_schema = t2.table_schema ');
set @q := concat(@q, ' and table_name = t2.table_name ');
set @q := concat(@q, ' and column_name = t2.column_name ) ');
set @q := concat(@q, ' order by rand() ');
set @q := concat(@q, ' limit 1 ');
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
insert into joincols(table_name1,table_name2,column_name1,column_name2) select va1,va2,@column_name1,@column_name2;
select * from joincols;
-- ③リレーションシップ設定SQLの作成と実行
set wk6 := (select count(*) from joincols where column_name1 != '' and column_name2 != '');
if wk6 > 0 then
-- テーブル件数取得
set @q := concat(' select count(*) into @count from ',va1);
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
set wk1 := @count;
set @q := concat(' select count(*) into @count from ',va2);
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
set wk2 := @count;
-- va1 -> 件数が少ないテーブルのテーブル名
-- va2 -> 件数が多いテーブルのテーブル名
-- va3 -> 件数が少ないテーブルのカラム名
-- va4 -> 件数が多いテーブルのカラム名
-- wk3 -> 少ないほうの件数
-- wk4 -> 多いほうの件数
if wk1 < wk2 then
set va1 := (select table_name1 from joincols);
set va2 := (select table_name2 from joincols);
set va3 := (select column_name1 from joincols);
set va4 := (select column_name2 from joincols);
set wk3 := wk1;
set wk4 := wk2;
else
set va1 := (select table_name2 from joincols);
set va2 := (select table_name1 from joincols);
set va3 := (select column_name2 from joincols);
set va4 := (select column_name1 from joincols);
set wk3 := wk2;
set wk4 := wk1;
end if;
select va1,va2,va3,va4,wk3,wk4;
set @q := concat(' alter table ',va1,' add カラム1 integer ');
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
set @q := concat(' alter table ',va2,' add カラム2 integer ');
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
set wk5 := 0;
while wk5 <= wk3 do
set @q := concat(' update ',va1,' set カラム1 = ',wk5,' where カラム1 is null limit 1 ');
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
set wk5 := wk5+1;
end while;
set wk5 := 0;
while wk5 <= wk4 do
set @q := concat(' update ',va2,' set カラム2 = mod(',wk5,',',wk3,') where カラム2 is null limit 1 ');
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
set wk5 := wk5+1;
end while;
set @q := concat(' update ',va2,' set ',va4,' = ( select ',va3,' from ',va1,' where カラム1 = ',va2,'.カラム2 ) ');
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
set @q := concat(' alter table ',va1,' drop カラム1 ');
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
set @q := concat(' alter table ',va2,' drop カラム2 ');
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
end if;
end
//
delimiter ;
drop procedure main;
delimiter //
create procedure main()
begin
declare loop_num numeric default 5; -- proc_makeER実行回数
declare wk1 numeric default 1;
while wk1 <= loop_num do
call proc_makeER;
set wk1 = wk1+1;
end while;
end
//
delimiter ;
call main;
(19c)
set serveroutput on
create or replace procedure proc_makeER
as
-- テーブル名配列
type rec10 is record(
table_name varchar2(4000)
);
type ar10 is table of rec10 index by binary_integer;
tabnames ar10 := ar10();
-- 結合条件配列
type rec40 is record(
table_name1 varchar2(4000)
,table_name2 varchar2(4000)
,column_name1 varchar2(30)
,column_name2 varchar2(30)
);
type ar40 is table of rec40 index by binary_integer;
joincols ar40 := ar40();
-- ワーク変数
wk1 number;
wk2 number;
wk3 number;
va1 varchar2(4000);
va2 varchar2(4000);
va3 varchar2(4000);
va4 varchar2(4000);
-- SQL文
vsql varchar2(4000);
begin
-- ①テーブル名配列にテーブルを格納
wk1 := 1;
for c1 in ( select table_name from user_tables order by dbms_random.random fetch first 2 rows only ) loop
tabnames(wk1).table_name := c1.table_name;
wk1 := wk1+1;
end loop;
dbms_output.put_line('---tabnames---' );
for i in 1..tabnames.count loop
dbms_output.put_line( tabnames(i).table_name );
end loop;
-- ②結合条件配列の作成
vsql :='';
va1 := '';
va2 := '';
vsql := vsql || ' select t1.column_name column_name1, t2.column_name column_name2 ';
vsql := vsql || ' from user_tab_cols t1 ';
vsql := vsql || ' inner join user_tab_cols t2 ';
vsql := vsql || ' on t1.data_type = t2.data_type ';
vsql := vsql || ' and nvl(t1.data_length,-1) = nvl(t2.data_length,-1) ';
vsql := vsql || ' and nvl(t1.data_precision,-1) = nvl(t2.data_precision,-1) ';
vsql := vsql || ' and nvl(t1.data_scale,-1) = nvl(t2.data_scale,-1) ';
vsql := vsql || ' and t1.table_name = '''|| tabnames(1).table_name ||''' ';
vsql := vsql || ' and t2.table_name = '''|| tabnames(2).table_name ||''' ';
-- 主キーとユニークインデックスカラムは除外
vsql := vsql || ' and not exists ( select 1 ';
vsql := vsql || ' from user_indexes t3 ';
vsql := vsql || ' inner join user_ind_columns t4 ';
vsql := vsql || ' on t3.index_name = t4.index_name ';
vsql := vsql || ' where t3.UNIQUENESS = ''UNIQUE'' ';
vsql := vsql || ' and t4.table_name = t1.table_name ';
vsql := vsql || ' and t4.column_name = t1.column_name ) ';
vsql := vsql || ' and not exists ( select 1 ';
vsql := vsql || ' from user_indexes t3 ';
vsql := vsql || ' inner join user_ind_columns t4 ';
vsql := vsql || ' on t3.index_name = t4.index_name ';
vsql := vsql || ' where t3.UNIQUENESS = ''UNIQUE'' ';
vsql := vsql || ' and t4.table_name = t2.table_name ';
vsql := vsql || ' and t4.column_name = t2.column_name ) ';
vsql := vsql || ' order by dbms_random.random ';
vsql := vsql || ' fetch first 1 rows only ';
begin
execute immediate vsql into va1,va2;
exception when others then
return;
end;
joincols(1).table_name1 := tabnames(1).table_name;
joincols(1).table_name2 := tabnames(2).table_name;
joincols(1).column_name1 := va1;
joincols(1).column_name2 := va2;
dbms_output.put_line('---joincols---' );
for i in 1..joincols.count loop
dbms_output.put_line( joincols(i).table_name1 || ','||
joincols(i).table_name2 || ','||
joincols(i).column_name1 || ','||
joincols(i).column_name2 );
end loop;
-- ③リレーションシップ設定SQLの作成と実行
-- テーブル件数取得
vsql := ' select count(*) from ' || joincols(1).table_name1;
execute immediate vsql into wk1;
vsql := ' select count(*) from ' || joincols(1).table_name2;
execute immediate vsql into wk2;
-- va1 -> 件数が少ないテーブルのテーブル名
-- va2 -> 件数が多いテーブルのテーブル名
-- va3 -> 件数が少ないテーブルのカラム名
-- va4 -> 件数が多いテーブルのカラム名
-- wk3 -> 少ないほうの件数
if wk1 < wk2 then
va1 := joincols(1).table_name1;
va2 := joincols(1).table_name2;
va3 := joincols(1).column_name1;
va4 := joincols(1).column_name2;
wk3 := wk1;
else
va1 := joincols(1).table_name2;
va2 := joincols(1).table_name1;
va3 := joincols(1).column_name2;
va4 := joincols(1).column_name1;
wk3 := wk2;
end if;
vsql := ' alter table '|| va1 ||' add カラム1 integer ';
execute immediate vsql;
vsql := ' alter table '|| va2 ||' add カラム2 integer ';
execute immediate vsql;
vsql := ' update ' || va1 || ' set カラム1 = rownum - 1 ';
execute immediate vsql;
vsql := ' update ' || va2 || ' set カラム2 = mod(rownum-1,'|| wk3 ||') ';
execute immediate vsql;
vsql := ' update ' || va2 ||' set ' || va4 || ' = ( select ' || va3 || ' from ' || va1 || ' where カラム1 = ' || va2 || '.カラム2 ) ';
execute immediate vsql;
vsql := ' alter table ' || va1 || ' drop ( カラム1 ) ';
execute immediate vsql;
vsql := ' alter table ' || va2 || ' drop ( カラム2 ) ';
execute immediate vsql;
end;
/
sho error;
create or replace procedure main
as
loop_num number := 5; -- proc_makeER実行回数
begin
for i in 1..loop_num loop
proc_makeER;
end loop;
end;
/
show error;
exec main;
(15)
drop type if exists rec10 CASCADE;
create type rec10 as(
table_name varchar(4000)
);
drop type if exists rec40 CASCADE;
create type rec40 as(
table_name1 varchar(4000)
,table_name2 varchar(4000)
,column_name1 varchar(30)
,column_name2 varchar(30)
);
drop procedure proc_makeER;
create or replace procedure proc_makeER()
language plpgsql
as $$
declare
-- テーブル名配列
tabnames rec10;
-- 結合条件配列
joincols rec40;
-- ワーク変数
r1 record;
wk1 numeric;
wk2 numeric;
wk3 numeric;
va1 varchar(4000);
va2 varchar(4000);
va3 varchar(4000);
va4 varchar(4000);
-- SQL文
vsql varchar(4000);
begin
-- ①テーブル名配列にテーブルを格納
wk1 := 1;
for r1 in ( select tablename from pg_tables where schemaname = 'public' order by random() limit 2 ) loop
tabnames[wk1].table_name := r1.tablename;
wk1 := wk1+1;
end loop;
raise notice '---tabnames---';
for i in 1..array_upper(tabnames, 1) loop
raise notice 'tabnames[%]: %',i,tabnames[i].table_name;
end loop;
-- ②結合条件配列の作成
vsql := '';
va1 := '';
va2 := '';
vsql := vsql || ' select t1.column_name column_name1, t2.column_name column_name2 ';
vsql := vsql || ' from information_schema.columns t1 ';
vsql := vsql || ' inner join information_schema.columns t2 ';
vsql := vsql || ' on t1.data_type = t2.data_type ';
vsql := vsql || ' and coalesce(t1.character_maximum_length,-1) = coalesce(t2.character_maximum_length,-1) ';
vsql := vsql || ' and coalesce(t1.numeric_precision,-1) = coalesce(t2.numeric_precision,-1) ';
vsql := vsql || ' and coalesce(t1.numeric_scale,-1) = coalesce(t2.numeric_scale,-1) ';
vsql := vsql || ' and t1.table_catalog = ''test'' ';
vsql := vsql || ' and t1.table_schema = ''public'' ';
vsql := vsql || ' and t1.table_name = '''||tabnames[1].table_name||''' ';
vsql := vsql || ' and t2.table_catalog = ''test'' ';
vsql := vsql || ' and t2.table_schema = ''public'' ';
vsql := vsql || ' and t2.table_name = '''||tabnames[2].table_name||''' ';
-- 主キーとユニークインデックスカラムは除外
vsql := vsql || ' and not exists ( ';
vsql := vsql || ' select 1 ';
vsql := vsql || ' from pg_indexes ';
vsql := vsql || ' where schemaname = ''public'' ';
vsql := vsql || ' and tablename = t1.table_name ';
vsql := vsql || ' and indexdef like ''%UNIQUE%'' ';
vsql := vsql || ' and ( indexdef like ''%''|| t1.column_name ||'',%'' or indexdef like ''%''|| t1.column_name ||'')%'' ) ';
vsql := vsql || ' ) ';
vsql := vsql || ' and not exists ( ';
vsql := vsql || ' select 1 ';
vsql := vsql || ' from pg_indexes ';
vsql := vsql || ' where schemaname = ''public'' ';
vsql := vsql || ' and tablename = t2.table_name ';
vsql := vsql || ' and indexdef like ''%UNIQUE%'' ';
vsql := vsql || ' and ( indexdef like ''%''|| t2.column_name ||'',%'' or indexdef like ''%''|| t2.column_name ||'')%'' ) ';
vsql := vsql || ' ) ';
vsql := vsql || ' order by random() limit 1 ';
execute vsql into va1,va2;
-- raise notice 'va1=%,va2=%' ,va1, va2;
if va1 is null or va2 is null then
return;
end if;
joincols[1].table_name1 := tabnames[1].table_name;
joincols[1].table_name2 := tabnames[2].table_name;
joincols[1].column_name1 := va1;
joincols[1].column_name2 := va2;
-- ③リレーションシップ設定SQLの作成と実行
-- テーブル件数取得
wk1 := 0;
wk2 := 0;
vsql := 'select count(*) from ' || joincols[1].table_name1;
execute vsql into wk1;
vsql := 'select count(*) from ' || joincols[1].table_name2;
execute vsql into wk2;
-- va1 -> 件数が少ないテーブルのテーブル名
-- va2 -> 件数が多いテーブルのテーブル名
-- va3 -> 件数が少ないテーブルのカラム名
-- va4 -> 件数が多いテーブルのカラム名
-- wk3 -> 少ないほうの件数
if wk1 < wk2 then
va1 := joincols[1].table_name1;
va2 := joincols[1].table_name2;
va3 := joincols[1].column_name1;
va4 := joincols[1].column_name2;
wk3 := wk1;
else
va1 := joincols[1].table_name2;
va2 := joincols[1].table_name1;
va3 := joincols[1].column_name2;
va4 := joincols[1].column_name1;
wk3 := wk2;
end if;
raise notice 'va1=%,va2=%,va3=%,va4=%,wk3=%' ,va1, va2, va3, va4, wk3;
vsql := ' alter table '|| va1 ||' add カラム1 integer ';
execute vsql;
vsql := ' alter table '|| va2 ||' add カラム2 integer ';
execute vsql;
vsql := '';
vsql := vsql || ' update '||va1||' t0 set カラム1 = ( select count(t2.ctid) ';
vsql := vsql || ' from '||va1||' t1 left join '||va1||' t2 ';
vsql := vsql || ' on t1.ctid > t2.ctid ';
vsql := vsql || ' where t1.ctid = t0.ctid ) ';
execute vsql;
vsql := '';
vsql := vsql || ' update '||va2||' t0 set カラム2 = ( select mod(count(t2.ctid),'||wk3||') ';
vsql := vsql || ' from '||va2||' t1 left join '||va2||' t2 ';
vsql := vsql || ' on t1.ctid > t2.ctid ';
vsql := vsql || ' where t1.ctid = t0.ctid ) ';
execute vsql;
vsql := ' update ' || va2 ||' set ' || va4 || ' = ( select ' || va3 || ' from ' || va1 || ' where カラム1 = ' || va2 || '.カラム2 ) ';
execute vsql;
vsql := ' alter table ' || va1 || ' drop カラム1 ';
execute vsql;
vsql := ' alter table ' || va2 || ' drop カラム2 ';
execute vsql;
end;
$$;
create or replace procedure main()
language plpgsql
as $$
declare
loop_num numeric := 5; -- proc_makeER実行回数
begin
for i in 1..loop_num loop
call proc_makeER();
end loop;
end;
$$;
call main();
(2019)
create or alter procedure proc_makeER
as
begin
set nocount on;
-- ワーク変数
declare @wk1 numeric;
declare @wk2 numeric;
declare @wk3 numeric;
declare @wk4 numeric;
declare @va1 varchar(4000);
declare @va2 varchar(4000);
declare @va3 varchar(4000);
declare @va4 varchar(4000);
-- SQL文
declare @vsql nvarchar(4000);
-- テーブル名配列
declare @tabnames table(id int identity(1,1)
,table_name varchar(4000)
);
-- 結合条件配列
declare @joincols table(id int identity(1,1)
,table_name1 varchar(4000)
,table_name2 varchar(4000)
,column_name1 varchar(30)
,column_name2 varchar(30)
);
-- ①テーブル名配列にテーブルを格納
declare cur10 cursor for select table_name from INFORMATION_SCHEMA.TABLES order by newid();
set @wk1 = 1;
open cur10;
fetch next from cur10 into @va1;
while @@fetch_status = 0
begin
insert into @tabnames(table_name) values(@va1);
fetch next from cur10 into @va1;
set @wk1 = @wk1+1;
if @wk1 > 2
break;
end
close cur10;
deallocate cur10;
set @va1 = (select table_name from @tabnames where id = 1);
set @va2 = (select table_name from @tabnames where id = 2);
set @vsql = N'';
set @va3 ='';
set @va4 ='';
-- ②結合条件配列の作成
set @vsql = @vsql+ N' select top(1) @va3 = t1.name , @va4 = t2.name ';
set @vsql = @vsql+ N' from sys.columns t1 ';
set @vsql = @vsql+ N' inner join sys.columns t2 ';
set @vsql = @vsql+ N' on t1.system_type_id = t2.system_type_id ';
set @vsql = @vsql+ N' and coalesce(t1.max_length,-1) = coalesce(t2.max_length,-1) ';
set @vsql = @vsql+ N' and coalesce(t1.precision,-1) = coalesce(t2.precision,-1) ';
set @vsql = @vsql+ N' and coalesce(t1.scale,-1) = coalesce(t2.scale,-1) ';
set @vsql = @vsql+ N' and object_name(t1.object_id) ='''+@va1+''' ';
set @vsql = @vsql+ N' and object_name(t2.object_id) ='''+@va2+''' ';
-- 主キーとユニークインデックスカラムは除外
set @vsql = @vsql+ N' and not exists ( select 1 ';
set @vsql = @vsql+ N' from sys.indexes t3 ';
set @vsql = @vsql+ N' inner join sys.index_columns t4 ';
set @vsql = @vsql+ N' on t3.object_id = t4.object_id ';
set @vsql = @vsql+ N' and t3.index_id = t4.index_id ';
set @vsql = @vsql+ N' where t3.is_unique = 1 ';
set @vsql = @vsql+ N' and object_name(t3.object_id) = object_name(t1.object_id) ';
set @vsql = @vsql+ N' and col_name(t4.object_id,t4.column_id) = t1.name ) ';
set @vsql = @vsql+ N' and not exists ( select 1 ';
set @vsql = @vsql+ N' from sys.indexes t3 ';
set @vsql = @vsql+ N' inner join sys.index_columns t4 ';
set @vsql = @vsql+ N' on t3.object_id = t4.object_id ';
set @vsql = @vsql+ N' and t3.index_id = t4.index_id ';
set @vsql = @vsql+ N' where t3.is_unique = 1 ';
set @vsql = @vsql+ N' and object_name(t3.object_id) = object_name(t2.object_id) ';
set @vsql = @vsql+ N' and col_name(t4.object_id,t4.column_id) = t2.name ) ';
set @vsql = @vsql+ N' order by newid() ';
execute sp_executesql
@vsql,
N'@va3 varchar(4000) OUT,@va4 varchar(4000) OUT', @va3 OUT, @va4 OUT;
insert into @joincols(table_name1,table_name2,column_name1,column_name2) select @va1,@va2,@va3,@va4;
select * from @joincols;
-- ③リレーションシップ設定SQLの作成と実行
set @wk4 = (select count(*) from @joincols where column_name1 != '' and column_name2 != '');
if @wk4 = 0
return;
-- テーブル件数取得
set @vsql = N'select @wk1 = count(*) from ';
set @vsql = @vsql+ @va1;
execute sp_executesql
@vsql,
N'@wk1 numeric OUT', @wk1 OUT;
set @vsql = N'select @wk2 = count(*) from ';
set @vsql = @vsql+ @va2;
execute sp_executesql
@vsql,
N'@wk2 numeric OUT', @wk2 OUT;
-- va1 -> 件数が少ないテーブルのテーブル名
-- va2 -> 件数が多いテーブルのテーブル名
-- va3 -> 件数が少ないテーブルのカラム名
-- va4 -> 件数が多いテーブルのカラム名
-- wk3 -> 少ないほうの件数
if @wk1 < @wk2
begin
set @va1 = (select table_name1 from @joincols);
set @va2 = (select table_name2 from @joincols);
set @va3 = (select column_name1 from @joincols);
set @va4 = (select column_name2 from @joincols);
set @wk3 = @wk1;
end;
else
begin
set @va1 = (select table_name2 from @joincols);
set @va2 = (select table_name1 from @joincols);
set @va3 = (select column_name2 from @joincols);
set @va4 = (select column_name1 from @joincols);
set @wk3 = @wk2;
end;
print @va1+','+@va2+','+@va3+','+@va4+','+cast(@wk3 as varchar);
set @vsql = ' alter table '+@va1+' add カラム0 bigint identity(0,1) ';
execute(@vsql);
set @vsql = ' alter table '+@va2+' add カラム0 bigint identity(0,1) ';
execute(@vsql);
set @vsql = ' alter table '+@va1+' add カラム1 integer ';
execute(@vsql);
set @vsql = ' alter table '+@va2+' add カラム2 integer ';
execute(@vsql);
set @vsql = ' update '+@va1+' set カラム1 = カラム0 ';
execute(@vsql);
set @vsql = ' update '+@va2+' set カラム2 = カラム0 %'+cast(@wk3 as varchar);
execute(@vsql);
set @vsql = ' update '+@va2+' set '+@va4+' = ( select '+@va3+' from '+@va1+' where カラム1 = '+@va2+'.カラム2 ) ';
execute(@vsql);
set @vsql = ' alter table '+@va1+' drop column カラム1 ';
execute(@vsql);
set @vsql = ' alter table '+@va2+' drop column カラム2 ';
execute(@vsql);
set @vsql = ' alter table '+@va1+' drop column カラム0 ';
execute(@vsql);
set @vsql = ' alter table '+@va2+' drop column カラム0 ';
execute(@vsql);
end
go
create or alter procedure main
as
begin
declare @loop_num numeric = 5; -- proc_makeER実行回数
declare @wk1 numeric = 1;
while @wk1 <= @loop_num
begin
exec proc_makeER;
set @wk1 = @wk1+1;
end;
end
go
exec main
