升星——公布自动生成测试数据存储过程——昨夜小楼
create procedure cs @input varchar(max)asset nocount onif patindex('%[@#$]%',@input)=0 returnselect @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 syscolumnsselect @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 tinyintdeclare @yes bitdeclare @cid smallintdeclare @col varchar(1000)declare @max smallintdeclare @type sysnamedeclare @lenp smallintdeclare @lens smallintdeclare @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 endwhile 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 endset @sql=nullselect @sql=isnull(@sql+char(13)+char(10),char(13)+char(10))+'select * from '+name from @tabs order by idprint @sqlset nocount offgo
[解决办法]
ding/........
[解决办法]
沙发是永远都抢不到
[解决办法]
恭喜了!!!
[解决办法]
ding
[解决办法]
来学习，好东西！
[解决办法]
exec cs '@t11 2 3a b 2b'/*--> 测试数据: @t1declare @t1 table (1 varchar(1),2 varchar(1),3 varchar(2))insert into @t1select 'a','b','2b'select * from @t1*/
[解决办法]
接分
[解决办法]
太强大了！
[解决办法]
好长,
[解决办法]
恭喜
三星了
[解决办法]
好强
[解决办法]
恭喜
三星了
[解决办法]
好长
好大
好喜欢
[解决办法]
恭喜
三星了
[解决办法]
恭喜
学习
接分
[解决办法]
恭喜
学习
接分
[解决办法]
恭喜
学习
接分
？怎么不提示我恢复太快呢？
[解决办法]
学习，up！
[解决办法]
太强了.学习!
[解决办法]
小楼强啊
[解决办法]
good
[解决办法]
学习
另
恭喜
三星了
[解决办法]
很好很强大~
收藏学习帮顶!
[解决办法]
恭喜，恭喜！！！
[解决办法]