自动生成测试数据共享加强版

转载请标明出处，引用请保留头注视。第一版链接 ：

http://topic.csdn.net/u/20080516/15/3fcf4880-67e9-4a28-844d-05985db51215.html

 

---------------------------------------------------------------------

-- 自动生成测试数据共享加强版 

--  

-- 基本特性 

-- * 表前缀：@表变量、#临时表、$实体表，支持混合多表。 

-- * 无表名：@1...@n#1...#nT1...Tn  

-- * 无列名：C1...Cn  

-- * 少列名：Cm...Cn  

-- * 列分隔：空格和TAB键。 

-- * 列填充：数据左边对齐原则，数据不足右向填充NULL。 

-- * 占位符：分号(;)，控制数据位置，左向填充NULL。 

-- * 字符集：不考虑UNICODE/NCHAR，网页能够正常显示的应该没问题，网页不能正常显示的，UNICODE也没用。 

--  

-- 加强特性 

-- * 优化数据类型解析，基本上涵盖了所有的原始测试数据形式。 

-- * 逗号(,)不再作为列分隔符，添加指定列分隔符控制，具体方法见示例。 

-- * 连续(;;)或离散(;)的分号才会被解析为占位符，维持数据中的分号。 

--  

-- 解析类型 

-- * int  

-- * bigint  

-- * money (New)  

-- * numeric  

-- * float  

-- * datetime  

-- * varchar  

-- * varbinary (New)  

-- * uniqueidentifier (New)  

-- * sql_variant  

--  

-- Limpire(昨夜小楼)  

-------------------------------------------------------------------------------  

CREATE PROCEDURE CS#  

(  

  @Input varchar(8000)  

)  

AS 

 

SET NOCOUNT ON 

 

declare @tid int,@cid int,@pos int,@num int,@p int,@s int,@tb sysname,@spt varchar(20),@inf varchar(2000),@colvarchar(4000),@def sysname,@ent varchar(2),@sql varchar(8000)  

declare @tables table(id int identity primary key,name sysname)  

declare @data table(id int identity primary key,data varchar(8000),fc as nullif(left(nullif(data,''),charindex(' ',nullif(data,''))-1),'null'))  

declare @temp table(id int primary key,temp varchar(4000))  

declare @code table(id int primary key,code varchar(8000))  

 

--> 格式整理 

set @Input=replace(replace(replace(@Input collate Chinese_PRC_CS_AS_KS_WS,'',' '),char(9),' '),char(13),char(10))  

set @Input=char(10)+ltrim(rtrim(@Input))+char(10)  

while charindex(char(10)+char(10),@Input)>0 set @Input=replace(@Input,char(10)+char(10),char(10))  

set @Input=stuff(@Input,1,1,'')  

if @Input not like '[@#$]%' and @Input not like '0[@#$]%' return 

set @Input=replace(replace(@Input,'。','.'),'•','.')  

select top 94 n=identity(tinyint,33,1) into # from syscolumns  

select @Input=replace(@Input collate Chinese_PRC_CS_AS_KS_WS,nchar(n+65248),char(n)) from #  

while charindex('  ',@Input)>0 set @Input=replace(@Input,'  ',' ')  

set @Input=replace(@Input,char(10)+' ',char(10))  

set @Input=replace(@Input,' '+char(10),char(10))  

select @ent=char(13)+char(10),@pos=charindex(char(10),@Input)  

 

--> 解析数据 

while @Input<>'' 

  begin 

    select @tb=left(@Input,@pos-1),@tid=isnull(@tid,0)+1,@Input=stuff(@Input,1,@pos,''),@pos=charindex(char(10),@Input)  

    if @tb like '0%' set @tb=stuff(@tb,1,1,'')  

    else select @inf=left(@Input,@pos-1)+' ',@Input=stuff(@Input,1,@pos,''),@pos=charindex(char(10),@Input)  

    if @tb like '%/%' select @spt=stuff(@tb,1,charindex('/',@tb),''),@tb=left(@tb,charindex('/',@tb)-1)  

    if @tb like '$%' set @tb=stuff(@tb,1,1,'')  

    if @tb='' set @tb='T' 

    if len(@tb)=1 set @tb=@tb+ltrim(@tid)  

    if @tb like '[^@#]%' set @tb='['+@tb+']' 

    insert @tables select @tb  

    while @pos>0  

      begin 

        insert @data select left(@Input,@pos-1)+' ' 

        select @Input=stuff(@Input,1,@pos,''),@pos=charindex(char(10),@Input)  

        if @Input like '[@#$]%' or @Input like '0[@#$]%' break  

      end 

    if @spt is not null 

      begin 

        set @inf=replace(@inf,@spt,' ')  

        while charindex('  ',@inf)>0 set @inf=replace(@inf,'  ',' ')  

        update @data set data=replace(data,@spt,' ')  

        while exists (select 1 from @data where charindex('  ',data)>0) update @data set data=replace(data,'  ',' ')where charindex('  ',data)>0  

      end 

    delete @data where data not like '%[^ -]%' 

    insert @code select id,null from @data  

    while exists (select 1 from @data where data<>'')  

      begin 

        set @cid=isnull(@cid,0)+1  

        insert @temp select id,case when fc not like '%[^;]%' then null else fc end from @data  

        update @data set data=case when fc not like '%[^;]%' and len(fc)>1 then stuff(fc,1,1,'')+' ' else ''end+stuff(data,1,charindex(' ',data),'')  

        if exists (select 1 from @temp a join @data b on a.id=b.id and temp is not null and fc is not null andisdate(temp)=1 and isdate(fc)=1) and not exists (select 1 from @temp a join @data b on a.id=b.id and temp is not nulland fc is not null and isdate(temp+case when fc not like '%[^;]%' then '' else ' '+fc end)=0)  

        begin 

          update a set temp=temp+case when fc not like '%[^;]%' then '' else ' '+fc end from @temp a join @data b ona.id=b.id where temp is not null and fc is not null 

          update @data set data=case when fc not like '%[^;]%' and len(fc)>1 then stuff(fc,1,1,'')+' ' else ''end+stuff(data,1,charindex(' ',data),'')  

        end 

        select @num=max(datalength(temp)) from @temp where temp is not null 

        if @num is null set @def='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 (len(temp)>1 and temp like '0%' and temp not like '%[^0-9]%') or templike '%,%') set @def=@num  

            else if exists (select 1 from @temp where patindex('%[Ee]%',temp)>0) set @def='float' 

            else if exists (select 1 from @temp where len(replace(replace(replace(temp,'+',''),'-',''),'.',''))>@@max_precision) set @def=@num  

            else if exists (select 1 from @temp where temp like '%[^0-9.+-]%') set @def='money' 

            else if exists (select 1 from @temp where temp like '%.%')  

              begin 

                select @p=max(len(parsename(n,2))),@s=max(len(parsename(n,1))) from (select n+case when n like '%.'then ' ' when n like '%.%' then '' else '. ' end n from (select replace(replace(temp,'+',''),'-','')n from @temp wheretemp is not null) a) b  

                if @p+@s>@@max_precision set @def='float' 

                else set @def='numeric('+ltrim(@p+@s)+','+ltrim(@s)+')' 

              end 

            else if exists (select 1 from @temp where temp is not null and isdate(temp)=0 or len(temp)<>8)  

              begin 

                if exists (select 1 from @temp where cast(temp as numeric(38,0)) not between -9223372036854775808 and9223372036854775807) select @def='numeric('+ltrim(max(len(replace(replace(temp,'+',''),'-',''))))+',0)' from @tempwhere temp is not null 

                else if exists (select 1 from @temp where cast(temp as numeric(38,0)) not between -2147483648 and2147483647) set @def='bigint' 

                else set @def='int' 

              end 

            else set @def='datetime' 

          end 

        else if not exists (select 1 from @temp where temp is not null and isdate(temp)=0) set @def='datetime' 

        else if not exists (select 1 from @temp where temp not like '0x%' or temp like '0x%[^0-9a-f]%') select@def='varbinary('+ltrim((@num-3)/2+1)+')' 

        else if not exists (select 1 from @temp where temp not like replicate('[0-9a-z]',8)+replicate('[-]'+replicate('[0-9a-z]',4),3)+'[-]'+replicate('[0-9a-z]',12)) set @def='uniqueidentifier' 

        else set @def=@num  

        if isnumeric(@def)=1 set @def='varchar('+@def+')' 

        set @col=isnull(@col+',','')+'['+isnull(left(@inf,charindex(' ',@inf)-1),'C'+ltrim(@cid))+'] '+@def  

        select @inf=nullif(stuff(@inf,1,charindex(' ',@inf),''),''),@def=left(@def+'(',charindex('(',@def+'(')-1)  

        update a set code=isnull(code+',','select ')+case when @def in ('datetime','varchar','uniqueidentifier') thenisnull(''''+temp+'''','null') else isnull(temp,'null') end from @code a join @temp b on a.id=b.id  

        delete @temp 

      end 

    if @inf is not null 

      begin 

        update @code set code=code+replicate(',null',len(@inf)-len(replace(@inf,' ',''))+1)  

        set @col=@col+',['+replace(rtrim(@inf),' ','] sql_variant,[')+'] sql_variant' 

      end 

    select @sql=isnull(@sql+' union all'+@ent,'')+code from @code  

    print '--> 测试数据：'+@tb  

    print case when @tb like '@%' then 'declare '+@tb+' table' else 'if object_id('''+case when @tb like '#%' then'tempdb.dbo.' else '' end+@tb+''') is not null drop table '+@tb+@ent+'create table '+@tb end+'('+@col+')'+@ent+'insert '+@tb  

    print @sql  

    delete @data  

    delete @code  

    select @cid=null,@spt=null,@inf=null,@col=null,@sql=null 

  end 

 

--> 智能代码(略)  

select @sql=isnull(@sql+@ent,@ent)+'select * from '+name from @tables  

print @sql  

 

SET NOCOUNT OFF 

 

GO 

 

-------------------------------------------------------------------------------

-- 自动生成测试数据共享加强版

--

-- 基本特性

-- * 表前缀：@表变量、#临时表、$实体表，支持混合多表。

-- * 无表名：@1...@n#1...#nT1...Tn

-- * 无列名：C1...Cn

-- * 少列名：Cm...Cn

-- * 列分隔：空格和TAB键。

-- * 列填充：数据左边对齐原则，数据不足右向填充NULL。

-- * 占位符：分号(;)，控制数据位置，左向填充NULL。

-- * 字符集：不考虑UNICODE/NCHAR，网页能够正常显示的应该没问题，网页不能正常显示的，UNICODE也没用。

--

-- 加强特性

-- * 优化数据类型解析，基本上涵盖了所有的原始测试数据形式。

-- * 逗号(,)不再作为列分隔符，添加指定列分隔符控制，具体方法见示例。

-- * 连续(;;)或离散(;)的分号才会被解析为占位符，维持数据中的分号。

--

-- 解析类型

-- * int

-- * bigint

-- * money (New)

-- * numeric

-- * float

-- * datetime

-- * varchar

-- * varbinary (New)

-- * uniqueidentifier (New)

-- * sql_variant

--

-- Limpire(昨夜小楼)

-------------------------------------------------------------------------------

CREATE PROCEDURE CS#

(

  @Input varchar(8000)

)

AS

 

SET NOCOUNT ON

 

declare @tid int,@cid int,@pos int,@num int,@p int,@s int,@tb sysname,@spt varchar(20),@inf varchar(2000),@colvarchar(4000),@def sysname,@ent varchar(2),@sql varchar(8000)

declare @tables table(id int identity primary key,name sysname)

declare @data table(id int identity primary key,data varchar(8000),fc as nullif(left(nullif(data,''),charindex(' ',nullif(data,''))-1),'null'))

declare @temp table(id int primary key,temp varchar(4000))

declare @code table(id int primary key,code varchar(8000))

 

--> 格式整理

set @Input=replace(replace(replace(@Input collate Chinese_PRC_CS_AS_KS_WS,'',' '),char(9),' '),char(13),char(10))

set @Input=char(10)+ltrim(rtrim(@Input))+char(10)

while charindex(char(10)+char(10),@Input)>0 set @Input=replace(@Input,char(10)+char(10),char(10))

set @Input=stuff(@Input,1,1,'')

if @Input not like '[@#$]%' and @Input not like '0[@#$]%' return

set @Input=replace(replace(@Input,'。','.'),'•','.')

select top 94 n=identity(tinyint,33,1) into # from syscolumns

select @Input=replace(@Input collate Chinese_PRC_CS_AS_KS_WS,nchar(n+65248),char(n)) from #

while charindex('  ',@Input)>0 set @Input=replace(@Input,'  ',' ')

set @Input=replace(@Input,char(10)+' ',char(10))

set @Input=replace(@Input,' '+char(10),char(10))

select @ent=char(13)+char(10),@pos=charindex(char(10),@Input)

 

--> 解析数据

while @Input<>''

  begin

    select @tb=left(@Input,@pos-1),@tid=isnull(@tid,0)+1,@Input=stuff(@Input,1,@pos,''),@pos=charindex(char(10),@Input)

    if @tb like '0%' set @tb=stuff(@tb,1,1,'')

    else select @inf=left(@Input,@pos-1)+' ',@Input=stuff(@Input,1,@pos,''),@pos=charindex(char(10),@Input)

    if @tb like '%/%' select @spt=stuff(@tb,1,charindex('/',@tb),''),@tb=left(@tb,charindex('/',@tb)-1)

    if @tb like '$%' set @tb=stuff(@tb,1,1,'')

    if @tb='' set @tb='T'

    if len(@tb)=1 set @tb=@tb+ltrim(@tid)

    if @tb like '[^@#]%' set @tb='['+@tb+']'

    insert @tables select @tb

    while @pos>0

      begin

        insert @data select left(@Input,@pos-1)+' '

        select @Input=stuff(@Input,1,@pos,''),@pos=charindex(char(10),@Input)

        if @Input like '[@#$]%' or @Input like '0[@#$]%' break

      end

    if @spt is not null

      begin

        set @inf=replace(@inf,@spt,' ')

        while charindex('  ',@inf)>0 set @inf=replace(@inf,'  ',' ')

        update @data set data=replace(data,@spt,' ')

        while exists (select 1 from @data where charindex('  ',data)>0) update @data set data=replace(data,'  ',' ')where charindex('  ',data)>0

      end

    delete @data where data not like '%[^ -]%'

    insert @code select id,null from @data

    while exists (select 1 from @data where data<>'')

      begin

        set @cid=isnull(@cid,0)+1

        insert @temp select id,case when fc not like '%[^;]%' then null else fc end from @data

        update @data set data=case when fc not like '%[^;]%' and len(fc)>1 then stuff(fc,1,1,'')+' ' else ''end+stuff(data,1,charindex(' ',data),'')

        if exists (select 1 from @temp a join @data b on a.id=b.id and temp is not null and fc is not null andisdate(temp)=1 and isdate(fc)=1) and not exists (select 1 from @temp a join @data b on a.id=b.id and temp is not nulland fc is not null and isdate(temp+case when fc not like '%[^;]%' then '' else ' '+fc end)=0)

        begin

          update a set temp=temp+case when fc not like '%[^;]%' then '' else ' '+fc end from @temp a join @data b ona.id=b.id where temp is not null and fc is not null

          update @data set data=case when fc not like '%[^;]%' and len(fc)>1 then stuff(fc,1,1,'')+' ' else ''end+stuff(data,1,charindex(' ',data),'')

        end

        select @num=max(datalength(temp)) from @temp where temp is not null

        if @num is null set @def='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 (len(temp)>1 and temp like '0%' and temp not like '%[^0-9]%') or templike '%,%') set @def=@num

            else if exists (select 1 from @temp where patindex('%[Ee]%',temp)>0) set @def='float'

            else if exists (select 1 from @temp where len(replace(replace(replace(temp,'+',''),'-',''),'.',''))>@@max_precision) set @def=@num

            else if exists (select 1 from @temp where temp like '%[^0-9.+-]%') set @def='money'

            else if exists (select 1 from @temp where temp like '%.%')

              begin

                select @p=max(len(parsename(n,2))),@s=max(len(parsename(n,1))) from (select n+case when n like '%.'then ' ' when n like '%.%' then '' else '. ' end n from (select replace(replace(temp,'+',''),'-','')n from @temp wheretemp is not null) a) b

                if @p+@s>@@max_precision set @def='float'

                else set @def='numeric('+ltrim(@p+@s)+','+ltrim(@s)+')'

              end

            else if exists (select 1 from @temp where temp is not null and isdate(temp)=0 or len(temp)<>8)

              begin

                if exists (select 1 from @temp where cast(temp as numeric(38,0)) not between -9223372036854775808 and9223372036854775807) select @def='numeric('+ltrim(max(len(replace(replace(temp,'+',''),'-',''))))+',0)' from @tempwhere temp is not null

                else if exists (select 1 from @temp where cast(temp as numeric(38,0)) not between -2147483648 and2147483647) set @def='bigint'

                else set @def='int'

              end

            else set @def='datetime'

          end

        else if not exists (select 1 from @temp where temp is not null and isdate(temp)=0) set @def='datetime'

        else if not exists (select 1 from @temp where temp not like '0x%' or temp like '0x%[^0-9a-f]%') select@def='varbinary('+ltrim((@num-3)/2+1)+')'

        else if not exists (select 1 from @temp where temp not like replicate('[0-9a-z]',8)+replicate('[-]'+replicate('[0-9a-z]',4),3)+'[-]'+replicate('[0-9a-z]',12)) set @def='uniqueidentifier'

        else set @def=@num

        if isnumeric(@def)=1 set @def='varchar('+@def+')'

        set @col=isnull(@col+',','')+'['+isnull(left(@inf,charindex(' ',@inf)-1),'C'+ltrim(@cid))+'] '+@def

        select @inf=nullif(stuff(@inf,1,charindex(' ',@inf),''),''),@def=left(@def+'(',charindex('(',@def+'(')-1)

        update a set code=isnull(code+',','select ')+case when @def in ('datetime','varchar','uniqueidentifier') thenisnull(''''+temp+'''','null') else isnull(temp,'null') end from @code a join @temp b on a.id=b.id

        delete @temp

      end

    if @inf is not null

      begin

        update @code set code=code+replicate(',null',len(@inf)-len(replace(@inf,' ',''))+1)

        set @col=@col+',['+replace(rtrim(@inf),' ','] sql_variant,[')+'] sql_variant'

      end

    select @sql=isnull(@sql+' union all'+@ent,'')+code from @code

    print '--> 测试数据：'+@tb

    print case when @tb like '@%' then 'declare '+@tb+' table' else 'if object_id('''+case when @tb like '#%' then'tempdb.dbo.' else '' end+@tb+''') is not null drop table '+@tb+@ent+'create table '+@tb end+'('+@col+')'+@ent+'insert '+@tb

    print @sql

    delete @data

    delete @code

    select @cid=null,@spt=null,@inf=null,@col=null,@sql=null

  end

 

--> 智能代码(略)

select @sql=isnull(@sql+@ent,@ent)+'select * from '+name from @tables

print @sql

 

SET NOCOUNT OFF

 

GO

--------------------------------------------------------------------------------

 

加强版使用示例

view plaincopy to clipboardprint?

--> 多表 

exec cs# '@var/,  

id data  

1, 表变量@  

2, 指定逗号为列分隔符 

3  空格和TAB是默认列分隔符 

#tmp  

id data  

-- -------  

1  临时表#  

2  忽略分隔横线 

3  默认逗号不是列分隔符：a,b,c,d  

$tab  

id   data  

---- -------  

1    实体表$  

2    保留null值 

null null'  

go  

 

--> 无表名/无列名(表前缀@#$前用修饰)  

exec cs# '@  

id data  

1 无表名 

0#tmp  

2 无列名 

0$  

3 双无'  

go  

 

--> 动态列名：列名不足动态添加Cm...Cn，数据左边对齐原则。 

exec cs# '@dynamic_column_name  

a  

11 12 13  

   21 22  

      31'  

go  

 

--> 动态填充：数据列不足，右向填充NULL值。 

exec cs# '@dynamic_fill_null  

a  b  c  d  e  

11 12 13  

   21 22  

      31'  

go  

 

--> 占位符：控制数据的位置，左向填充NULL值。 

exec cs# '@semicolon  

a  b  c  d  e  

11 12 13  

;  21 22  

;  ;  31'  

go  

 

--> 占位符增强特性：数据中的分号不会解析为占位符。 

exec cs# '@semicolon  

a b c d  

; ;; 连续;;或离散;才会被解析为占位符'  

go  

 

--> 时间解析 

exec cs# '@time 

id date boy  

1 2001-1-1 Mark  

2 20020101 John  

3 23:15:39 Paul'  

go  

 

--> 时间解析 

exec 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  

 

--> 基本上可以覆盖所有的原始测试数据形式 

exec cs# '0@data_type  

001 1,2,3 1.0E10 123456789012345678901234567890123456789 $9.99  9.99 9223372036854775808                   -9223372036854775808 -2147483648 20080101 2008-07-05 23:59:59.000 0x1234 681000D3-0E3A-49ED-8F59-80973A021B8E A  

002 4,5,6 2.0E11 111111111111111111111111111111111111111 99.99 99.99 99999999999999999999999999999999999999 9223372036854775807  2147483647 20081231 2008-07-06 00:00:00.000 0xabcd 09E80A5C-7699-4BC9-AEF4-B0BCCF2E03E9 B'  

go  

 

/*  

PS  

表前缀$和美元符号$冲突，如美元货币类型出现在第一列会报错。 

$99.9这种原始测试数据形式实属罕见，有需要者自行修改表前缀定义。 

*/ 

 

--> 多表

exec cs# '@var/,

id data

1, 表变量@

2, 指定逗号为列分隔符

3  空格和TAB是默认列分隔符

#tmp

id data

-- -------

1  临时表#

2  忽略分隔横线

3  默认逗号不是列分隔符：a,b,c,d

$tab

id   data

---- -------

1    实体表$

2    保留null值

null null'

go

 

--> 无表名/无列名(表前缀@#$前用修饰)

exec cs# '@

id data

1 无表名

0#tmp

2 无列名

0$

3 双无'

go

 

--> 动态列名：列名不足动态添加Cm...Cn，数据左边对齐原则。

exec cs# '@dynamic_column_name

a

11 12 13

   21 22

      31'

go

 

--> 动态填充：数据列不足，右向填充NULL值。

exec cs# '@dynamic_fill_null

a  b  c  d  e

11 12 13

   21 22

      31'

go

 

--> 占位符：控制数据的位置，左向填充NULL值。

exec cs# '@semicolon

a  b  c  d  e

11 12 13

;  21 22

;  ;  31'

go

 

--> 占位符增强特性：数据中的分号不会解析为占位符。

exec cs# '@semicolon

a b c d

; ;; 连续;;或离散;才会被解析为占位符'

go

 

--> 时间解析

exec cs# '@time

id date boy

1 2001-1-1 Mark

2 20020101 John

3 23:15:39 Paul'

go

 

--> 时间解析

exec 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

 

--> 基本上可以覆盖所有的原始测试数据形式

exec cs# '0@data_type

001 1,2,3 1.0E10 123456789012345678901234567890123456789 $9.99  9.99 9223372036854775808                   -9223372036854775808 -2147483648 20080101 2008-07-05 23:59:59.000 0x1234 681000D3-0E3A-49ED-8F59-80973A021B8E A

002 4,5,6 2.0E11 111111111111111111111111111111111111111 99.99 99.99 99999999999999999999999999999999999999 9223372036854775807  2147483647 20081231 2008-07-06 00:00:00.000 0xabcd 09E80A5C-7699-4BC9-AEF4-B0BCCF2E03E9 B'

go

 

/*

PS

表前缀$和美元符号$冲突，如美元货币类型出现在第一列会报错。

$99.9这种原始测试数据形式实属罕见，有需要者自行修改表前缀定义。

*/

Limpire

 

2008-07-07

 

本文来自CSDN博客，转载请标明出处：

http://blog.csdn.net/Limpire/archive/2008/07/07/2623016.aspx