(8.0.22)
仕様：
パラメータ引数1: データ件数
パラメータ引数2: テーブル名
ディクショナリからテーブル一覧を取得
各テーブルごとに、カラム名、データ型、データ長さを取得
各テーブルごとに、主キー先頭カラム名、主キー現在最大値を取得
データ型に応じてランダム値をセットするinsert文を作成 ※主キー制約の先頭カラムについては重複しない連番をセットする
作成したinsert文を指定回数実行。 ※重複値エラーはメッセージ出力
drop procedure proc_ins;
delimiter //
create procedure proc_ins(in param1 int,in param2 varchar(100) )
begin
declare sql_text varchar(4000);
declare tab_name varchar(30);
declare pcolname varchar(30);
declare maxval varchar(100);
declare colnames varchar(4000);
declare date_flg int;
-- カーソル定義
declare vdone1 int;
declare cur1 cursor for select table_name from information_schema.tables
where table_schema=database()
and table_name = coalesce(param2,table_name)
order by table_name;
declare continue handler for sqlstate '02000' set vdone1 = 1;
set vdone1 = 0;
open cur1;
fetch cur1 into tab_name;
while vdone1 != 1 do
select tab_name;
set date_flg = 0;
-- insert文のカラム部分作成
select group_concat(column_name order by ordinal_position separator ',') into colnames
from information_schema.columns
where table_schema = database()
and table_name = tab_name;
set sql_text := concat('insert into ',tab_name,'(',colnames,') select ');
-- 主キー先頭カラムの取得
begin
declare continue handler for sqlstate '02000' set pcolname = NULL ;
select column_name into pcolname
from information_schema.statistics
where table_schema = database()
and table_name = tab_name
and index_name = 'PRIMARY'
order by seq_in_index
limit 1;
end;
select pcolname;
-- decimal型の場合、numeric_precision - numeric_scale に格納可能な整数部分の桁数が入る
-- int型の場合、最大値2147483647
-- bigint型の場合、最大値9223372036854775807
-- char,varchar型の場合、character_maximum_lengthに格納可能な文字長が入る
begin
declare vdone2 int;
declare v_column_name varchar(64);
declare v_data_type longtext;
declare v_character_maximum_length bigint;
declare v_numeric_precision bigint unsigned;
declare v_numeric_scale bigint unsigned;
declare cur2 cursor for select column_name,data_type,character_maximum_length,numeric_precision,numeric_scale
from information_schema.columns
where table_schema = database()
and table_name = tab_name
order by ordinal_position;
declare continue handler for sqlstate '02000' set vdone2 = 1;
set vdone2 = 0;
open cur2;
fetch cur2 into v_column_name,v_data_type,v_character_maximum_length,v_numeric_precision,v_numeric_scale;
while vdone2 != 1 do
if pcolname = v_column_name then
set @maxval := null;
case
when v_data_type in ('int','bigint','decimal') then
set @sql_text := concat('select cast(max(',pcolname,') as char) into @maxval from ', tab_name);
prepare stmt from @sql_text;
execute stmt ;
deallocate prepare stmt;
set maxval := coalesce(@maxval,'0');
set sql_text := concat(sql_text,'?,');
when v_data_type in ('char','varchar') then
set @sql_text := concat('select cast(max(cast(',pcolname,' as decimal) ) as char) into @maxval from ',tab_name,' where regexp_like(',pcolname,', ''^[0-9]+$'')');
prepare stmt from @sql_text;
execute stmt ;
deallocate prepare stmt;
set maxval := coalesce(@maxval,'0');
set sql_text := concat(sql_text,'lpad(?,',v_character_maximum_length,',''0''),');
when v_data_type in ('date','datetime','timestamp') then
set @sql_text := concat('select cast(max(UNIX_TIMESTAMP(',pcolname,') ) as char) into @maxval from ',tab_name);
prepare stmt from @sql_text;
execute stmt ;
deallocate prepare stmt;
set maxval := coalesce(@maxval,'946652400');
set sql_text := concat(sql_text,'FROM_UNIXTIME(?),');
if v_data_type in ('date') then
set date_flg = 1;
end if;
else set sql_text := concat(sql_text,'NULL,');
end case;
else
case
when v_data_type = 'int' then
set sql_text := concat(sql_text,'floor(rand() * 2147483647),');
when v_data_type = 'bigint' then
set sql_text := concat(sql_text,'floor(rand() * 9223372036854775807),');
when v_data_type = 'decimal' then
set sql_text := concat(sql_text,'floor(rand() * power(10,(', cast((v_numeric_precision - v_numeric_scale) as char) ,') ) ),');
when v_data_type in ('char','varchar') then
set sql_text := concat(sql_text,'substring(md5(rand() ), 1, ',cast(v_character_maximum_length as char),'),');
when v_data_type in ('date','datetime','timestamp') then
set sql_text := concat(sql_text,'date_add(''2000-01-01'', interval floor(7600 * rand() ) day),');
else set sql_text := concat(sql_text,'NULL,');
end case;
end if;
fetch cur2 into v_column_name,v_data_type,v_character_maximum_length,v_numeric_precision,v_numeric_scale;
end while;
close cur2;
end;
set sql_text := substring(sql_text,1, length(sql_text) -1);
select sql_text;
-- データ作成ループ
begin
declare continue handler for sqlstate '23000' begin select 'ER_DUP_KEY'; end;
set @i := 1;
if date_flg = 1 then
set @maxval := cast((cast(maxval as decimal) + 24*3600) as char);
else
set @maxval := cast((cast(maxval as decimal) + 1) as char);
end if;
set @sql_text := sql_text;
while @i <= param1 do
prepare stmt from @sql_text;
if pcolname is not null then
execute stmt using @maxval;
else
execute stmt;
end if;
deallocate prepare stmt;
set @i := @i + 1;
if date_flg = 1 then
set @maxval := cast((cast(@maxval as decimal) + 24*3600) as char);
else
set @maxval := cast((cast(@maxval as decimal) + 1) as char);
end if;
end while;
end;
fetch cur1 into tab_name;
end while;
close cur1;
end
//
delimiter ;
-- テスト用SQL
select concat('drop table ',table_name,';') from information_schema.tables where table_schema=database();
create table tab11(col1 int primary key,col2 varchar(10),col3 datetime);
create table tab12(col1 varchar(10) primary key,col2 datetime,col3 int);
create table tab13(col1 datetime primary key,col2 int,col3 varchar(10) );
create table tab14(col1 datetime primary key,col2 int,col3 varchar(10) );
create table tab15(col1 date primary key,col2 int,col3 varchar(10) );
create table tab21(col1 int,col2 varchar(10),col3 datetime);
create table tab22(col1 varchar(10),col2 datetime,col3 int);
create table tab23(col1 datetime,col2 int,col3 varchar(10) );
create table tab31(col1 int,col2 varchar(10),col3 datetime);
alter table tab31 add constraint tab31pk primary key(col1,col2);
create table tab41(
col1 int,
col2 bigint,
col3 decimal(10),
col4 decimal(10,2),
col5 varchar(10),
col6 char(10),
col7 date,
col8 datetime,
col9 timestamp,
col10 blob,
col11 text);
call proc_ins(10,null);
call proc_ins(10,'tab11');
select * from tab11;
select * from tab12;
select * from tab13;
select * from tab14;
select * from tab15;
select * from tab21;
select * from tab22;
select * from tab23;
select * from tab31;
select * from tab41;
insert into tab12 values('AAA',CURDATE(),1);
insert into tab12 values('4444444',CURDATE(),1);
call proc_ins(10,null);
call proc_ins(300,null);
(19c)
仕様：
パラメータ引数1: データ件数
パラメータ引数2: テーブル名
ディクショナリからテーブル一覧を取得
各テーブルごとに、カラム名、データ型、データ長さを取得
各テーブルごとに、主キー先頭カラム名、主キー現在最大値を取得
データ型に応じてランダム値をセットするinsert文を作成 ※主キー制約の先頭カラムについては重複しない連番をセットする
作成したinsert文を指定回数実行。 ※重複値エラーはメッセージ出力
set serveroutput on
create or replace procedure proc_ins(param1 in number default 100,param2 in varchar2 default null)
authid current_user
as
sql_text varchar2(4000);
pcolname varchar2(30);
maxval varchar2(100);
sql_text2 varchar2(4000);
int_flg int;
char_flg int;
date_flg int;
timestamp_flg int;
cursor cur1 is select table_name
from user_tables
where table_name not like 'SYS%'
and table_name = nvl(param2,table_name)
order by table_name;
cursor cur2(vparam1 in varchar2) is select column_name,data_type,data_length,data_precision,data_scale
from user_tab_columns where table_name = vparam1 order by column_id;
begin
for c1 in cur1 loop
dbms_output.put_line(c1.table_name);
int_flg := 0;
char_flg := 0;
date_flg := 0;
timestamp_flg := 0;
sql_text :='insert into ' || c1.table_name || '(';
for c2 in cur2(c1.table_name) loop
sql_text := sql_text || c2.column_name || ',';
end loop;
sql_text := substr(sql_text,1, length(sql_text) -1) || ') select ' ;
begin
select T2.column_name into pcolname
from user_constraints T1
inner join user_ind_columns T2
on T1.index_name = T2.index_name
where T1.constraint_type = 'P'
and T2.column_position = 1
and T1.table_name = c1.table_name
;
exception
when others then
pcolname := NULL;
end;
dbms_output.put_line('pcolname='||pcolname);
-- number型の場合、data_precision - data_scale に格納可能な整数部分の桁数が入る
-- char,varchar2型の場合、data_lengthに格納可能なバイト長が入る
-- nchar,nvarchar2型の場合、data_lengthに格納可能なバイト長が入る。これを1/2した値が最大文字長となる。
for c2 in cur2(c1.table_name) loop
if pcolname = c2.column_name then
maxval := NULL;
case
when c2.data_type = 'NUMBER' then
sql_text2 := 'select to_char(max(' || pcolname || ') ) from ' || c1.table_name;
begin
execute immediate sql_text2 into maxval;
exception
when others then
raise;
end;
if maxval is null then
maxval := '0';
end if;
sql_text := sql_text || 'to_char(:maxval),';
int_flg := 1;
when c2.data_type in ('CHAR','VARCHAR2','NCHAR','NVARCHAR2') then
sql_text2 := 'select to_char(max(to_number(' || pcolname || ') ) ) from ' || c1.table_name || ' where regexp_like( ' || pcolname || ',''^[0-9]+$'')';
begin
execute immediate sql_text2 into maxval;
exception
when others then
raise;
end;
if maxval is null then
maxval := '0';
end if;
if c2.data_type in ('CHAR','VARCHAR2') then
sql_text := sql_text || 'lpad(:maxval,'||c2.data_length||',''0''),';
else
sql_text := sql_text || 'lpad(:maxval,'||c2.data_length/2||',''0''),';
end if;
char_flg := 1;
when c2.data_type = 'DATE' then
sql_text2 := 'select max(to_char(' || pcolname || ',''YYYYMMDD'') ) from ' || c1.table_name;
begin
execute immediate sql_text2 into maxval;
exception
when others then
raise;
end;
if maxval is null then
maxval := '20200101';
end if;
sql_text := sql_text || 'to_date(:maxval,''YYYYMMDD''),';
date_flg := 1;
when c2.data_type like 'TIMESTAMP%' then
sql_text2 := 'select max(to_char(' || pcolname || ',''YYYYMMDDHH24MISS'') ) from ' || c1.table_name;
begin
execute immediate sql_text2 into maxval;
exception
when others then
raise;
end;
if maxval is null then
maxval := '20200101000000';
end if;
sql_text := sql_text || 'to_date(:maxval,''YYYYMMDDHH24MISS''),';
timestamp_flg := 1;
else sql_text := sql_text || 'NULL,';
end case;
else
case
when c2.data_type = 'NUMBER' then
sql_text := sql_text || 'floor(dbms_random.value(1, power(10,'|| to_char(nvl(c2.data_precision,9) - nvl(c2.data_scale,0) ) ||') ) ),';
when c2.data_type in ('CHAR','VARCHAR2','NCHAR','NVARCHAR2') then
sql_text := sql_text || 'substr(standard_hash(dbms_random.value(), ''MD5''),1,'||
case when c2.data_type in ('NCHAR','NVARCHAR2') then to_char(nvl(c2.data_length/2,1) ) else to_char(nvl(c2.data_length,1) ) end
||'),';
when c2.data_type = 'DATE' or c2.data_type like 'TIMESTAMP%' then
sql_text := sql_text || 'to_date(''20000101'',''yyyymmdd'') + floor(dbms_random.value(0, 7600) ),';
else sql_text := sql_text || 'NULL,';
end case;
end if;
end loop;
sql_text := substr(sql_text,1, length(sql_text) -1) || ' from dual' ;
dbms_output.put_line(sql_text);
for i in 1..param1 loop
if pcolname is not null then
-- データ型に応じたインクリメント
if int_flg = 1 then
maxval := to_char(to_number(maxval) + 1);
elsif char_flg = 1 then
maxval := to_char(to_number(maxval) + 1);
elsif date_flg = 1 then
maxval := to_char((to_date(maxval,'YYYYMMDD') + 1),'YYYYMMDD');
elsif timestamp_flg = 1 then
maxval := to_char((to_date(maxval,'YYYYMMDDHH24MISS') + 1/24/3600),'YYYYMMDDHH24MISS');
end if;
end if;
begin
if pcolname is not null and ( int_flg = 1 or char_flg = 1 or date_flg = 1 or timestamp_flg = 1 ) then
execute immediate sql_text using maxval;
else
execute immediate sql_text;
end if;
exception
when DUP_VAL_ON_INDEX then
dbms_output.put_line('DUP_VAL_ON_INDEX !');
when others then
raise;
end;
end loop;
commit;
end loop;
end;
/
show error;
-- テスト用SQL
select 'drop table ' || table_name || ' purge;' from user_tables;
create table tab11(col1 int primary key,col2 varchar2(10),col3 timestamp);
create table tab12(col1 varchar2(10) primary key,col2 timestamp,col3 int);
create table tab13(col1 timestamp primary key,col2 int,col3 varchar2(10) );
create table tab14(col1 date primary key,col2 int,col3 varchar2(10) );
create table tab21(col1 int,col2 varchar2(10),col3 timestamp);
create table tab22(col1 varchar2(10),col2 timestamp,col3 int);
create table tab23(col1 timestamp,col2 int,col3 varchar2(10) );
create table tab31(col1 int,col2 varchar2(10),col3 timestamp);
alter table tab31 add constraint tab31pk primary key(col1,col2);
create table tab41(
col1 int,
col2 number(10),
col3 number(10,2),
col4 varchar2(10),
col5 char(10),
col6 nvarchar2(10),
col7 nchar(10),
col8 date,
col9 timestamp,
col10 blob,
col11 clob);
exec proc_ins();
exec proc_ins(10);
exec proc_ins(10,NULL);
exec proc_ins(10,'TAB11');
select * from tab11;
select * from tab12;
select * from tab13;
select * from tab14;
select * from tab21;
select * from tab22;
select * from tab23;
select * from tab31;
select * from tab41;
insert into tab12 values('AAA',sysdate,1);
insert into tab12 values('4444444',sysdate,1);
exec proc_ins(10,NULL);
exec proc_ins(300,NULL);
(13)
仕様：
パラメータ引数1: データ件数
パラメータ引数2: テーブル名
ディクショナリからテーブル一覧を取得
各テーブルごとに、カラム名、データ型、データ長さを取得
各テーブルごとに、主キー先頭カラム名、主キー現在最大値を取得
データ型に応じてランダム値をセットするinsert文を作成 ※主キー制約の先頭カラムについては重複しない連番をセットする
作成したinsert文を指定回数実行。 ※重複値エラーはメッセージ出力
create or replace procedure proc_ins(param1 in int default 100, param2 in varchar(100) default null)
language plpgsql
as $$
declare
sql_text varchar(4000);
pcolname varchar(30);
maxval varchar(100);
sql_text2 varchar(4000);
int_flg int;
char_flg int;
date_flg int;
timestamp_flg int;
cur1 cursor for select tablename from pg_tables
where schemaname = 'public'
and tablename = coalesce(param2,tablename)
order by tablename;
cur2 cursor(vparam1 varchar) for select column_name,data_type,character_maximum_length,numeric_precision,numeric_scale
from information_schema.columns
where table_schema = 'public'
and table_name = vparam1
order by ordinal_position;
begin
for c1 in cur1 loop
raise notice 'c1.tablename=%', c1.tablename;
int_flg := 0;
char_flg := 0;
date_flg := 0;
timestamp_flg := 0;
sql_text :='insert into ' || c1.tablename || '(';
for c2 in cur2(c1.tablename) loop
sql_text := sql_text || c2.column_name || ',';
end loop;
sql_text := substr(sql_text,1, length(sql_text) -1) || ') select ' ;
begin
select
case
when position(',' in T1.indexdef) > 0 then
substring(T1.indexdef from (position('(' in T1.indexdef) )+1 for (position(',' in T1.indexdef) - position('(' in T1.indexdef)-1) )
else
substring(T1.indexdef from (position('(' in T1.indexdef) )+1 for (position(')' in T1.indexdef) - position('(' in T1.indexdef)-1) )
end into pcolname
from pg_indexes T1
inner join information_schema.table_constraints T2
on T1.schemaname = T2.table_schema
and T1.tablename = T2.table_name
and T2.constraint_type = 'PRIMARY KEY'
and T1.indexname = T2.constraint_name
where T1.schemaname = 'public'
and T1.tablename = c1.tablename
;
exception
when others then
pcolname := NULL;
end;
raise notice 'pcolname=%', pcolname;
-- numeric型の場合、numeric_precision - numeric_scale に格納可能な整数部分の桁数が入る
-- integer型の場合、最大値2147483647
-- bigint型の場合、最大値9223372036854775807
-- char,varchar型の場合、character_maximum_lengthに格納可能な文字長が入る
for c2 in cur2(c1.tablename) loop
if pcolname = c2.column_name then
maxval := null;
case
when c2.data_type in ('integer','bigint','numeric') then
sql_text2 := 'select max(' || pcolname || ') from ' || c1.tablename;
begin
execute sql_text2 into maxval;
exception
when others then
raise;
end;
if maxval is null then
maxval := '0';
end if;
sql_text := sql_text || '$1::bigint,';
int_flg = 1;
when c2.data_type like 'character%' then
sql_text2 := 'select (max(' || pcolname || '::numeric) )::varchar from ' || c1.tablename || ' where ' || pcolname || ' ~ ''^[0-9]+$''';
begin
execute sql_text2 into maxval;
exception
when others then
raise;
end;
if maxval is null then
maxval := '0';
end if;
sql_text := sql_text || 'lpad($1,' ||c2.character_maximum_length ||',''0''),';
char_flg = 1;
when c2.data_type = 'date' then
sql_text2 := 'select max(to_char(' || pcolname || ',''YYYY/MM/DD'') ) from ' || c1.tablename;
begin
execute sql_text2 into maxval;
exception
when others then
raise;
end;
if maxval is null then
maxval := '2020/01/01';
end if;
sql_text := sql_text || '$1::date,';
date_flg = 1;
when c2.data_type like 'timestamp%' then
sql_text2 := 'select max(to_char(' || pcolname || ',''YYYY/MM/DD HH24:MI:SS'') ) from ' || c1.tablename;
begin
execute sql_text2 into maxval;
exception
when others then
raise;
end;
if maxval is null then
maxval := '2020/01/01/ 00:00:00';
end if;
sql_text := sql_text || '$1::timestamp,';
timestamp_flg=1;
else sql_text := sql_text || 'NULL,';
end case;
else
case
when c2.data_type = 'integer' then
sql_text := sql_text || 'floor(random() * 2147483647),';
when c2.data_type = 'bigint' then
sql_text := sql_text || 'floor(random() * 9223372036854775807),';
when c2.data_type = 'numeric' then
sql_text := sql_text || 'floor(random() * power(10, ' || (c2.numeric_precision - c2.numeric_scale)::varchar || ') ),';
when c2.data_type like 'character%' then
sql_text := sql_text || 'substr(md5(random()::text), 1, ' || c2.character_maximum_length::varchar || '),';
when c2.data_type = 'date' or c2.data_type like 'timestamp%' then
sql_text := sql_text || '''2000-01-01''::date + CAST( floor(7600 * random() ) || ''days'' AS interval),';
else sql_text := sql_text || 'NULL,';
end case;
end if;
end loop;
sql_text := substr(sql_text,1, length(sql_text) -1);
raise notice 'sql_text=%', sql_text;
for i in 1..param1 loop
if pcolname is not null then
-- データ型に応じたインクリメント
if int_flg = 1 then
maxval := (maxval::bigint + 1)::varchar;
elseif char_flg = 1 then
maxval := (maxval::bigint + 1)::varchar;
elseif date_flg = 1 then
maxval := (maxval::date + '1 day'::interval)::varchar;
elseif timestamp_flg = 1 then
maxval := (maxval::timestamp + '1 sec'::interval)::varchar;
end if;
end if;
begin
if pcolname is not null and ( int_flg = 1 or char_flg= 1 or date_flg = 1 or timestamp_flg = 1 ) then
execute sql_text using maxval;
else
execute sql_text;
end if;
exception
when unique_violation then
raise notice 'unique_violation !';
when others then
raise;
end;
end loop;
commit;
end loop;
end;
$$;
-- テスト用SQL
select 'drop table ' || tablename || ' ;' from pg_tables where schemaname = 'public';
create table tab11(col1 int primary key,col2 varchar(10),col3 timestamp);
create table tab12(col1 varchar(10) primary key,col2 timestamp,col3 int);
create table tab13(col1 timestamp primary key,col2 int,col3 varchar(10) );
create table tab14(col1 date primary key,col2 int,col3 varchar(10) );
create table tab21(col1 int,col2 varchar(10),col3 timestamp);
create table tab22(col1 varchar(10),col2 timestamp,col3 int);
create table tab23(col1 timestamp,col2 int,col3 varchar(10) );
create table tab31(col1 int,col2 varchar(10),col3 timestamp);
alter table tab31 add constraint tab31pk primary key(col1,col2);
create table tab41(
col1 int,
col2 bigint,
col3 numeric(10),
col4 numeric(10,2),
col5 varchar(10),
col6 char(10),
col7 date,
col8 timestamp,
col9 bytea,
col10 text);
call proc_ins();
call proc_ins(10);
call proc_ins(10,null);
call proc_ins(10,'tab11');
select * from tab11;
select * from tab12;
select * from tab13;
select * from tab14;
select * from tab21;
select * from tab22;
select * from tab23;
select * from tab31;
select * from tab41;
insert into tab12 values('AAA',clock_timestamp(),1);
insert into tab12 values('4444444',clock_timestamp(),1);
call proc_ins(10,null);
call proc_ins(300,null);
(2019)
仕様：
パラメータ引数1: データ件数
パラメータ引数2: テーブル名
ディクショナリからテーブル一覧を取得
各テーブルごとに、カラム名、データ型、データ長さを取得
各テーブルごとに、主キー先頭カラム名、主キー現在最大値を取得
データ型に応じてランダム値をセットするinsert文を作成 ※主キー制約の先頭カラムについては重複しない連番をセットする
作成したinsert文を指定回数実行。 ※重複値エラーはメッセージ出力
create or alter procedure proc_ins(@param1 int = 100,@param2 varchar(100) = null)
as
begin
set nocount on;
declare @sql_text nvarchar(4000);
declare @pcolname varchar(30);
declare @sql_text2 nvarchar(4000);
declare @params nvarchar(4000);
declare @tab_name varchar(30);
declare @colnames varchar(4000);
declare @i bigint;
declare @int_flg int;
declare @char_flg int;
declare @date_flg int;
declare @datetime2_flg int;
declare @maxval_int bigint;
declare @maxval_char varchar(100);
declare @maxval_date date;
declare @maxval_datetime2 datetime2;
declare cur1 cursor for select name from sys.tables where name = coalesce(@param2,name) order by name;
open cur1;
fetch next from cur1 into @tab_name;
while @@fetch_status = 0
begin
print @tab_name;
set @int_flg = 0;
set @char_flg = 0;
set @date_flg = 0;
set @datetime2_flg = 0;
-- insert文のカラム部分作成
set @colnames = ( select string_agg( name ,',') within group ( order by column_id )
from sys.columns
where object_name(object_id) = @tab_name
);
set @sql_text = 'insert into '+@tab_name+'('+@colnames+') select ';
-- 主キー先頭カラムの取得
set @pcolname = ( select top 1 col_name(B.object_id,B.column_id)
from sys.indexes A
inner join sys.index_columns B
on A.object_id = B.object_id
and A.index_id = B.index_id
where object_name(A.object_id) = @tab_name
and A.is_primary_key = 1
);
print @pcolname;
-- decimal,numeric型の場合、presicion - scale に格納可能な整数部分の桁数が入る
-- int型の場合、最大値2147483647
-- bigint型の場合、最大値9223372036854775807
-- char,varchar型の場合、max_lengthに格納可能なバイト長が入る(ただし、maxの場合は-1)
-- nchar,nvarchar型の場合、max_lengthに格納可能なバイト長が入る。これを1/2した値が最大文字長となる。(ただし、maxの場合は-1)
begin
declare @colname varchar(100);
declare @system_type_id int;
declare @max_length int;
declare @precision int;
declare @scale int;
declare @max_length2 int;
declare cur2 cursor for select name,system_type_id,max_length,precision,scale
from sys.columns
where object_name(object_id) = @tab_name
order by column_id
open cur2;
fetch next from cur2 into @colname,@system_type_id,@max_length,@precision,@scale;
while @@fetch_status = 0
begin
-- int-> 56 bigint-> 127 numeric-> 108 decimal-> 106
-- char-> 175 varchar-> 167 nchar-> 239 nvarchar-> 231
-- date-> 40 datetime2-> 42
if @pcolname = @colname
begin
set @maxval_int = null;
set @maxval_char = null;
set @maxval_date = null;
set @maxval_datetime2 = null;
if @system_type_id in (56,127,108,106)
begin
set @sql_text2 = 'select @maxval_int = max('+@pcolname+') from '+@tab_name;
set @params = '@maxval_int varchar(100) OUT';
execute sp_executesql @sql_text2, @params, @maxval_int OUT;
set @maxval_int = coalesce(@maxval_int,0);
set @sql_text = @sql_text + '@maxval_int,';
set @int_flg = 1;
end
else if @system_type_id in (175,167,239,231)
begin
set @sql_text2 = 'select @maxval_char = cast(max(cast('+@pcolname+' as bigint) ) as varchar) from '+ @tab_name +' where ' + @pcolname +' not like ''%[^0-9]%''';
set @params = '@maxval_char varchar(100) OUT';
execute sp_executesql @sql_text2, @params, @maxval_char OUT;
set @maxval_char = coalesce(@maxval_char,'0');
set @sql_text = @sql_text + 'right(''000000000000000000000000000000''+@maxval_char,'+cast(@max_length as varchar)+'),';
set @char_flg = 1;
end
else if @system_type_id = 40
begin
set @sql_text2 = 'select @maxval_date = max('+@pcolname+') from '+@tab_name
set @params = '@maxval_date varchar(100) OUT';
execute sp_executesql @sql_text2, @params, @maxval_date OUT;
set @maxval_date = coalesce(@maxval_date,cast('20000101' as date) );
set @sql_text = @sql_text + '@maxval_date,';
set @date_flg = 1;
end
else if @system_type_id =42
begin
set @sql_text2 = 'select @maxval_datetime2 = max('+@pcolname+') from '+@tab_name
set @params = '@maxval_datetime2 varchar(100) OUT';
execute sp_executesql @sql_text2, @params, @maxval_datetime2 OUT;
set @maxval_datetime2 = coalesce(@maxval_datetime2,cast('20000101 00:00:00' as datetime2) );
set @sql_text = @sql_text + '@maxval_datetime2,';
set @datetime2_flg = 1;
end
else set @sql_text = @sql_text+'NULL,';
end;
else
begin
if @system_type_id = 56
set @sql_text = @sql_text + 'floor(rand() * 2147483647),';
else if @system_type_id = 127
set @sql_text = @sql_text + 'floor(rand() * 9223372036854775807),';
else if @system_type_id in (108,106)
set @sql_text = @sql_text + 'floor(rand() * power(cast(10 as bigint),cast(' + cast((@precision - @scale) as varchar) + ' as bigint) ) ),';
else if @system_type_id in (175,167,239,231)
begin
if @max_length = -1
set @max_length2 = 1000;
else if @system_type_id in (239,231)
set @max_length2 = @max_length/2;
else set @max_length2 = @max_length;
set @sql_text = @sql_text + 'substring(master.dbo.fn_varbintohexstr(HASHBYTES(''MD5'',cast(rand() as varchar) ) ),3,' + cast(@max_length2 as varchar) + '),';
end;
else if @system_type_id in (40,42)
set @sql_text = @sql_text + 'dateadd(day,floor(7600 * rand() ),''2000-01-01''),';
else set @sql_text = @sql_text+'NULL,';
end;
fetch next from cur2 into @colname,@system_type_id,@max_length,@precision,@scale;
end
close cur2;
deallocate cur2;
end;
set @sql_text = substring(@sql_text,1,len(@sql_text)-1);
print @sql_text;
-- データ作成ループ
set @i = 1;
while (@i <= @param1)
begin
if @pcolname is not null
begin
-- データ型に応じたインクリメント
if @int_flg = 1
begin
set @params = '@maxval_int bigint';
set @maxval_int = @maxval_int + 1;
end;
else if @char_flg = 1
begin
set @params = '@maxval_char varchar(100)';
set @maxval_char = cast((cast(@maxval_char as bigint) + 1) as varchar);
end;
else if @date_flg = 1
begin
set @params = '@maxval_date date';
set @maxval_date = dateadd(day,1,@maxval_date);
end;
else if @datetime2_flg = 1
begin
set @params = '@maxval_datetime2 datetime2';
set @maxval_datetime2 = dateadd(second,1,@maxval_datetime2);
end;
end;
begin try
if @pcolname is not null
begin
if @int_flg = 1
begin
execute sp_executesql @sql_text, @params, @maxval_int;
end;
else if @char_flg = 1
begin
execute sp_executesql @sql_text, @params, @maxval_char;
end;
else if @date_flg = 1
begin
execute sp_executesql @sql_text, @params, @maxval_date;
end;
else if @datetime2_flg = 1
begin
execute sp_executesql @sql_text, @params, @maxval_datetime2;
end;
else
begin
execute (@sql_text)
end;
end;
else
begin
execute (@sql_text)
end;
end try
begin catch
if error_number() = 2627
print 'primary key violation';
else throw;
end catch;
set @i = @i + 1;
end;
fetch next from cur1 into @tab_name;
end
close cur1;
deallocate cur1;
end
go
-- テスト用SQL
select 'drop table ' + name + ' ;' from sys.tables;
create table tab11(col1 int primary key,col2 varchar(10),col3 datetime2);
create table tab12(col1 varchar(10) primary key,col2 datetime2,col3 int);
create table tab13(col1 datetime2 primary key,col2 int,col3 varchar(10) );
create table tab14(col1 date primary key,col2 int,col3 varchar(10) );
create table tab21(col1 int,col2 varchar(10),col3 datetime2);
create table tab22(col1 varchar(10),col2 datetime2,col3 int);
create table tab23(col1 datetime2,col2 int,col3 varchar(10) );
create table tab31(col1 int not null,col2 varchar(10) not null,col3 datetime2);
alter table tab31 add constraint tab31pk primary key(col1,col2);
create table tab41(
col1 int,
col2 bigint,
col3 numeric(10),
col4 numeric(10,2),
col5 decimal(10),
col6 decimal(10,2),
col7 varchar(10),
col8 char(10),
col9 nvarchar(10),
col10 nchar(10),
col11 date,
col12 datetime2,
col13 binary(10),
col14 varbinary(10),
col15 varchar(max),
col16 varbinary(max)
);
exec proc_ins;
exec proc_ins 10;
exec proc_ins 10, null;
exec proc_ins 10, 'tab11';
select * from tab11;
select * from tab12;
select * from tab13;
select * from tab14;
select * from tab21;
select * from tab22;
select * from tab23;
select * from tab31;
select * from tab41;
insert into tab12 values('AAA',GETDATE(),1);
insert into tab12 values('4444444',GETDATE(),1);
exec proc_ins 10, null;
exec proc_ins 300, null;
