升星——公布自动生成测试数据存储过程——昨夜小楼

Question

create procedure cs
@input varchar(max)
as
set nocount on
if patindex('%[@#$]%',@input)=0 return
select @input=replace(@input,'　',' '),@input=ltrim(rtrim(@input))
select top 94 code=identity(tinyint,33,1),m=cast(null as varchar(2)),w=cast(null as varchar(1)) into # from syscolumns
select @input=replace(@input collate chinese_prc_cs_as_ks_ws,nchar(code+65248),char(code)) from #
truncate table #
insert into # select char(13),char(10)
insert into # select char(9) ,','
insert into # select ' ',','
insert into # select '|' ,','
insert into # select '¦' ,','
insert into # select '。','.'
insert into # select '·','.'
insert into # select char(39),''
select @input=replace(@input collate chinese_prc_cs_as_ks_ws,m,w) from #
drop table #
while patindex('%[^,][;]%',@input)>0 set @input=stuff(@input,patindex('%[^,][;]%',@input)+1,1,',;')
while patindex('%[;][^,]%',@input)>0 set @input=stuff(@input,patindex('%[;][^,]%',@input),1,';,')
set @input=replace(@input,';','null')
while charindex(',,',@input)>0 set @input=replace(@input,',,',',')
set @input=replace(@input,char(10)+',',char(10))
set @input=replace(@input,','+char(10),char(10))
while charindex(char(10)+char(10),@input)>0 set @input=replace(@input,char(10)+char(10),char(10))
if left(@input,1)=char(10) set @input=right(@input,len(@input)-1)
if right(@input,1)<>char(10) set @input=@input+char(10)
declare @tab  sysname -- 表名：@=变量表；#=临时表；$=实体表
declare @tid  tinyint
declare @yes  bit
declare @cid  smallint
declare @col  varchar(1000)
declare @max  smallint
declare @type sysname
declare @lenp smallint
declare @lens smallint
declare @sql  varchar(8000)
declare @tabs table (id int identity,name sysname)
declare @data table (id int identity,data varchar(8000))
declare @temp table (id int,temp varchar(1000))
declare @code table (id int,code varchar(8000))
if charindex('$',@input)>0
begin
set @tab=substring(@input,charindex('$',@input)+1,charindex(char(10),@input,charindex('$',@input))-charindex('$',@input)-1)
if object_id(@tab) is not null
begin
raiserror(N'数据库中已存在名为 ''%s'' 的对象。',16,1,@tab)
return
end
end
while patindex('%[@#$]%',@input)>0
begin
select @tab=left(@input,charindex(char(10),@input)-1),@tid=isnull(@tid,0)+1,@yes=1,@cid=1,@input=right(@input,len(@input)-charindex(char(10),@input))
if left(@tab,1)='0' select @tab=stuff(@tab,1,1,''),@yes=0
if len(@tab)=1 set @tab=@tab+'T'+ltrim(@tid)
if left(@tab,1)='$' set @tab=quotename(stuff(@tab,1,1,''))
insert into @tabs values (@tab)
if @yes=0 set @col=null
else select @col=left(@input,charindex(char(10),@input)-1)+',',@input=right(@input,len(@input)-charindex(char(10),@input)),@col=replace(@col,',',':')
while charindex(char(10),@input)>0
begin
insert into @data select left(@input,charindex(char(10),@input)-1)
set @input=right(@input,len(@input)-charindex(char(10),@input))
if left(@input,1) in ('@','#','$') or left(@input,2) in ('0@','0#','0$') break
end
delete from @data where patindex('%[^,-]%',data)=0
select @max=max(len(data)-len(replace(data,',',''))) from @data
update @data set data=data+replicate(',null',@max-len(data)+len(replace(data,',','')))+','
set @max=isnull(len(@col)-len(replace(@col,':','')),0)
insert into @code select id,null from @data order by id
while exists (select 1 from @data where charindex(',',data)>0)
begin
insert into @temp select id,nullif(left(data,charindex(',',data)-1),'null') from @data order by id
update @data set data=right(data,len(data)-charindex(',',data))
if exists (select 1 from @temp a inner join @data b on a.id=b.id and a.temp is not null and left(b.data,4) not in ('','null')) and not exists (select 1 from @temp a inner join @data b on a.id=b.id and a.temp is not null and left(b.data,4) not in ('','null') and isdate(a.temp+space(1)+left(b.data,charindex(',',b.data)-1))=0)
begin
update a set a.temp=a.temp+space(1)+left(b.data,charindex(',',b.data)-1) from @temp a inner join @data b on a.id=b.id where a.temp is not null and left(b.data,charindex(',',b.data)-1)<>'null'
update @data set data=right(data,len(data)-charindex(',',data))
end
if not exists (select 1 from @temp where temp is not null) set @type=' sql_variant'
else if not exists (select 1 from @temp where temp is not null and isnumeric(temp)=0)
begin
if exists (select 1 from @temp where patindex('%[Ee]%',temp)>0) set @type=' float'
else if exists (select 1 from @temp where charindex('.',temp)>0)
begin
select @lenp=max(charindex('.',case when left(temp,1)='-' then right(temp,len(temp)-1) else temp end))-1,@lens=max(charindex('.',reverse(temp)))-1 from @temp where charindex('.',temp)>0
if @lenp+@lens>@@max_precision set @type=' float'
else set @type=' numeric('+ltrim(@lenp+@lens)+','+ltrim(@lens)+')'
end
else if exists (select 1 from @temp where len(temp)>1 and left(temp,1)='0') select @type=' varchar('+ltrim(max(datalength(temp)))+')' from @temp where temp is not null
else if exists (select 1 from @temp where temp is not null and len(temp)<>8 or isdate(temp)=0)
begin
select @lenp=isnull(max(len(temp)),0) from @temp where left(temp,1)<>'-'
select @lens=-isnull(max(len(temp)-1),0) from @temp where left(temp,1)='-'
if @lenp <= abs(@lens) select @lenp=@lenp+@lens,@lens=@lenp-@lens,@lenp=@lenp-@lens
if abs(@lenp)>38 set @type=' varchar('+ltrim(case when @lenp>0 then @lenp else abs(@lenp)+1 end)+')'
else if exists (select 1 from @temp where cast(temp as numeric(38,0)) not between -9223372036854775808 and 9223372036854775807) set @type=' numeric('+ltrim(abs(@lenp))+',0)'
else if exists (select 1 from @temp where cast(temp as numeric(38,0)) not between -2147483648 and 2147483647) set @type=' bigint'
else set @type=' int'
end
else set @type=' datetime'
end
else if not exists (select 1 from @temp where temp is not null and isdate(temp)=0) set @type=' datetime'
else select @type=' varchar('+ltrim(max(datalength(temp)))+')' from @temp where temp is not null
if charindex(':',@col)>0 set @col=stuff(@col,charindex(':',@col),1,@type+case when @cid=@max then '' else ',' end)
else set @col=isnull(@col+',','')+'c'+ltrim(@cid)+@type
update a set a.code=isnull(a.code+',','select ')+case when substring(@type,2,3) in ('flo','num','big','int') then isnull(b.temp,'null') else isnull(quotename(b.temp,char(39)),'null') end from @code a inner join @temp b on a.id=b.id
delete from @temp
set @cid=@cid+1
end
if @max>@cid-1
begin
select @col=stuff(@col,len(@col),1,' sql_variant'),@col=replace(@col,':',' sql_variant,')
update @code set code=code+replicate(',null',@max-@cid+1)
end
set @sql=null
select @sql=isnull(@sql+' union all'+char(13)+char(10),'')+code from @code order by id
print '--> 测试数据: '+@tab
print case left(@tab,1) when '@' then 'declare '+@tab+' table' when '#' then 'if object_id(''tempdb.dbo.'+@tab+''') is not null drop table '+@tab+char(13)+char(10)+'create table '+@tab else 'if object_id('''+@tab+''') is not null drop table '+@tab+char(13)+char(10)+'create table '+@tab end+' ('+@col+')'
print 'insert into '+@tab
print @sql
delete from @data
delete from @code
end
set @sql=null
select @sql=isnull(@sql+char(13)+char(10),char(13)+char(10))+'select * from '+name from @tabs order by id
print @sql
set nocount off
go

Answer 1

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * 功能：根据提问者提供的原始数据，生成测试数据表。                          *
 * 说明：1.单个/多个测试表一起生成。                                         *
 *       2.表类型：变量表/临时表/实体表，可控。                              *
 *       3.如无表名：T1...Tn；如无列名：c1...cn。                            *
 *       4.列填充：数据列不足填充NULL值；字段列不足以sql_variant填充cm...cn。*
 *       5.数据类型解析：自动解析最恰当的数据类型/长度。                     *
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

Answer 2

--> 多表测试：
cs '@var
id data
1 表变量#tmp
id data
2 临时表$tab
id,data
3 实体表'
go/*
--> 测试数据: @var
declare @var table (id int,data varchar(6))
insert into @var
select 1,'表变量'
--> 测试数据: #tmp
if object_id('tempdb.dbo.#tmp') is not null drop table #tmp
create table #tmp (id int,data varchar(6))
insert into #tmp
select 2,'临时表'
--> 测试数据: [tab]
if object_id('[tab]') is not null drop table [tab]
create table [tab] (id int,data varchar(6))
insert into [tab]
select 3,'实体表'select * from @var
select * from #tmp
select * from [tab]
*/

Answer 3

--> 无表名/无列名测试：
cs '@
id data
1 无表名0#tmp
2 无列名0$
3 双无'
go/*
--> 测试数据: @T1
declare @T1 table (id int,data varchar(6))
insert into @T1
select 1,'无表名'
--> 测试数据: #tmp
if object_id('tempdb.dbo.#tmp') is not null drop table #tmp
create table #tmp (c1 int,c2 varchar(6))
insert into #tmp
select 2,'无列名'
--> 测试数据: [T3]
if object_id('[T3]') is not null drop table [T3]
create table [T3] (c1 int,c2 varchar(4))
insert into [T3]
select 3,'双无'select * from @T1
select * from #tmp
select * from [T3]
*/--> 保留原NULL值，字段数据分隔线忽略：
cs '@null
a    b
---- ----
11   null
null 22'
go

Answer 4

/*
--> 测试数据: @null
declare @null table (a int,b int)
insert into @null
select 11,null union all
select null,22select * from @null
*/

Answer 5

--> 数据列不足，NULL值填充，数据左边对齐原则：
cs '@fill_null
a b c d
11 12 13
   21 22
      31'
go/*
--> 测试数据: @fill_null
declare @fill_null table (a int,b int,c int,d sql_variant)
insert into @fill_null
select 11,12,13,null union all
select 21,22,null,null union all
select 31,null,null,nullselect * from @fill_null
*/

Answer 6

--> 字段名不足，动态添加字段，以cm...cn命名：
cs '@add_column
a
11 12 13
   21 22
      31'
go/*
--> 测试数据: @add_column
declare @add_column table (a int,c2 int,c3 int)
insert into @add_column
select 11,12,13 union all
select 21,22,null union all
select 31,null,nullselect * from @add_column
*/

Answer 7

--> 占位符分号的作用——控制数据的位置，用NULL代替效果一样：
cs '@semicolon
a b c
11 12 13
;  22 23
;  ;  33'
go/*
--> 测试数据: @semicolon
declare @semicolon table (a int,b int,c int)
insert into @semicolon
select 11,12,13 union all
select null,22,23 union all
select null,null,33select * from @semicolon
*/

Answer 8

-->时间解析一：
cs '@time
id date boy
1 2001-1-1 Mark
2 20020101 John
3 23:15:39 Paul'
go/*
--> 测试数据: @time
declare @time table (id int,date datetime,boy varchar(4))
insert into @time
select 1,'2001-1-1','Mark' union all
select 2,'20020101','John' union all
select 3,'23:15:39','Paul'select * from @time
*/

Answer 9

-->时间解析二：
cs '@time
id date boy
1 2001-1-1 12:28:47 Mark
2 20020101 17:30:00 John
3 2003/1/1 23:15:39 Paul'
go/*
--> 测试数据: @time
declare @time table (id int,date datetime,boy varchar(4))
insert into @time
select 1,'2001-1-1 12:28:47','Mark' union all
select 2,'20020101 17:30:00','John' union all
select 3,'2003/1/1 23:15:39','Paul'select * from @time
*/

Answer 10

-->时间解析三——不支持：
cs '@time
id date boy
1 2001-1-1 12:28:47 Mark
2 20020101 John
3 2003/1/1 Paul'
go/*
--> 测试数据: @time
declare @time table (id int,date datetime,boy varchar(8),c4 varchar(4))
insert into @time
select 1,'2001-1-1','12:28:47','Mark' union all
select 2,'20020101','John',null union all
select 3,'2003/1/1','Paul',nullselect * from @time
*/-->时间解析三——正确解析需要手工添加占位符：
cs '@time
id date boy
1 2001-1-1 12:28:47 Mark
2 20020101 ; John
3 2003/1/1 ; Paul'
go/*
--> 测试数据: @time
declare @time table (id int,date datetime,boy varchar(4))
insert into @time
select 1,'2001-1-1 12:28:47','Mark' union all
select 2,'20020101','John' union all
select 3,'2003/1/1','Paul'select * from @time
*/

Answer 11

exec cs '@time
id date boy
1 2002-01-01 00:01:02 John'
go

Answer 12

有完整發行版麽？How much?
買來搶分不錯！^_^

Answer 13

测到一个Bug，数字作列名没有处理：
exec cs '@t1
1 2 3
a b 2b'/*
--> 测试数据: @t1
declare @t1 table (1 varchar(1),2 varchar(1),3 varchar(2))
insert into @t1
select 'a','b','2b'select * from @t1
*/

Answer 14

哦，倒忽略了这个问题，谢谢dobear_0922。

Answer 15

-->存储过程第60行：
else select @col=left(@input,charindex(char(10),@input)-1)+',',@input=right(@input,len(@input)-charindex(char(10),@input)),@col=replace(@col,',',':')
-->改为：
else select @col=left(@input,charindex(char(10),@input)-1),@input=right(@input,len(@input)-charindex(char(10),@input)),@col='['+replace(@col,',',']:[')+']:'

Answer 16

exec cs '@t1
1 2 3
a b 2b'/*
--> 测试数据: @t1
declare @t1 table ([1] varchar(1),[2] varchar(1),[3] varchar(2))
insert into @t1
select 'a','b','2b'select * from @t1
*/

Answer 17

    [align=center]==== 思想重于技巧 ====
[/align]

reply via CSDN viewer