自动生成测试数据加强版

加强版存储过程

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

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

---

[sql]  view plain copy

1. -------------------------------------------------------------------------------  
2. -- 自动生成测试数据共享加强版  
3. --  
4. -- 基本特性  
5. -- * 表前缀：@表变量、#临时表、$实体表，支持混合多表。  
6. -- * 无表名：@1...@n　#1...#n　T1...Tn  
7. -- * 无列名：C1...Cn  
8. -- * 少列名：Cm...Cn  
9. -- * 列分隔：空格和TAB键。  
10. -- * 列填充：数据左边对齐原则，数据不足右向填充NULL。  
11. -- * 占位符：分号(;)，控制数据位置，左向填充NULL。  
12. -- * 字符集：不考虑UNICODE/NCHAR，网页能够正常显示的应该没问题，网页不能正常显示的，UNICODE也没用。  
13. --  
14. -- 加强特性  
15. -- * 优化数据类型解析，基本上涵盖了所有的原始测试数据形式。  
16. -- * 逗号(,)不再作为列分隔符，添加指定列分隔符控制，具体方法见示例。  
17. -- * 连续(;;)或离散(;)的分号才会被解析为占位符，维持数据中的分号。  
18. --  
19. -- 解析类型  
20. -- * int  
21. -- * bigint  
22. -- * money (New)  
23. -- * numeric  
24. -- * float  
25. -- * datetime  
26. -- * varchar  
27. -- * varbinary (New)  
28. -- * uniqueidentifier (New)  
29. -- * sql_variant  
30. --  
31. -- Limpire(昨夜小楼)  
32. -------------------------------------------------------------------------------  
33. CREATE PROCEDURE CS#  
34. (  
35.   @Input varchar(8000)  
36. )  
37. AS  
38.   
39. SET NOCOUNT ON  
40.   
41. declare @tid int,@cid int,@pos int,@num int,@p int,@s int,@tb sysname,@spt varchar(20),@inf varchar(2000),@col varchar(4000),@def sysname,@ent varchar(2),@sql varchar(8000)  
42. declare @tables table(id int identity primary key,name sysname)  
43. declare @data table(id int identity primary key,data varchar(8000),fc as nullif(left(nullif(data,''),charindex(' ',nullif(data,''))-1),'null'))  
44. declare @temp table(id int primary key,temp varchar(4000))  
45. declare @code table(id int primary key,code varchar(8000))  
46.   
47. --> 格式整理  
48. set @Input=replace(replace(replace(@Input collate Chinese_PRC_CS_AS_KS_WS,'　',' '),char(9),' '),char(13),char(10))  
49. set @Input=char(10)+ltrim(rtrim(@Input))+char(10)  
50. while charindex(char(10)+char(10),@Input)>0 set @Input=replace(@Input,char(10)+char(10),char(10))  
51. set @Input=stuff(@Input,1,1,'')  
52. if @Input not like '[@#$]%' and @Input not like '0[@#$]%' return  
53. set @Input=replace(replace(@Input,'。','.'),'·','.')  
54. select top 94 n=identity(tinyint,33,1) into # from syscolumns  
55. select @Input=replace(@Input collate Chinese_PRC_CS_AS_KS_WS,nchar(n+65248),char(n)) from #  
56. while charindex('  ',@Input)>0 set @Input=replace(@Input,'  ',' ')  
57. set @Input=replace(@Input,char(10)+' ',char(10))  
58. set @Input=replace(@Input,' '+char(10),char(10))  
59. select @ent=char(13)+char(10),@pos=charindex(char(10),@Input)  
60.   
61. --> 解析数据  
62. while @Input<>''  
63.   begin  
64.     select @tb=left(@Input,@pos-1),@tid=isnull(@tid,0)+1,@Input=stuff(@Input,1,@pos,''),@pos=charindex(char(10),@Input)  
65.     if @tb like '0%' set @tb=stuff(@tb,1,1,'')  
66.     else select @inf=left(@Input,@pos-1)+' ',@Input=stuff(@Input,1,@pos,''),@pos=charindex(char(10),@Input)  
67.     if @tb like '%/%' select @spt=stuff(@tb,1,charindex('/',@tb),''),@tb=left(@tb,charindex('/',@tb)-1)  
68.     if @tb like '$%' set @tb=stuff(@tb,1,1,'')  
69.     if @tb='' set @tb='T'  
70.     if len(@tb)=1 set @tb=@tb+ltrim(@tid)  
71.     if @tb like '[^@#]%' set @tb='['+@tb+']'  
72.     insert @tables select @tb  
73.     while @pos>0  
74.       begin  
75.         insert @data select left(@Input,@pos-1)+' '  
76.         select @Input=stuff(@Input,1,@pos,''),@pos=charindex(char(10),@Input)  
77.         if @Input like '[@#$]%' or @Input like '0[@#$]%' break  
78.       end  
79.     if @spt is not null  
80.       begin  
81.         set @inf=replace(@inf,@spt,' ')  
82.         while charindex('  ',@inf)>0 set @inf=replace(@inf,'  ',' ')  
83.         update @data set data=replace(data,@spt,' ')  
84.         while exists (select 1 from @data where charindex('  ',data)>0) update @data set data=replace(data,'  ',' ') where charindex('  ',data)>0  
85.       end  
86.     delete @data where data not like '%[^ -]%'  
87.     insert @code select id,null from @data  
88.     while exists (select 1 from @data where data<>'')  
89.       begin  
90.         set @cid=isnull(@cid,0)+1  
91.         insert @temp select id,case when fc not like '%[^;]%' then null else fc end from @data  
92.         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),'')  
93.         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 and isdate(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 null and fc is not null and isdate(temp+case when fc not like '%[^;]%' then '' else ' '+fc end)=0)  
94.         begin  
95.           update a set temp=temp+case when fc not like '%[^;]%' then '' else ' '+fc end from @temp a join @data b on a.id=b.id where temp is not null and fc is not null  
96.           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),'')  
97.         end  
98.         select @num=max(datalength(temp)) from @temp where temp is not null  
99.         if @num is null set @def='sql_variant'  
100.         else if not exists (select 1 from @temp where temp is not null and isnumeric(temp)=0)  
101.           begin  
102.             if exists (select 1 from @temp where (len(temp)>1 and temp like '0%' and temp not like '%[^0-9]%') or temp like '%,%') set @def=@num  
103.             else if exists (select 1 from @temp where patindex('%[Ee]%',temp)>0) set @def='float'  
104.             else if exists (select 1 from @temp where len(replace(replace(replace(temp,'+',''),'-',''),'.',''))>@@max_precision) set @def=@num  
105.             else if exists (select 1 from @temp where temp like '%[^0-9.+-]%') set @def='money'  
106.             else if exists (select 1 from @temp where temp like '%.%')  
107.               begin  
108.                 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 where temp is not null) a) b  
109.                 if @p+@s>@@max_precision set @def='float'  
110.                 else set @def='numeric('+ltrim(@p+@s)+','+ltrim(@s)+')'  
111.               end  
112.             else if exists (select 1 from @temp where temp is not null and isdate(temp)=0 or len(temp)<>8)  
113.               begin  
114.                 if exists (select 1 from @temp where cast(temp as numeric(38,0)) not between -9223372036854775808 and 9223372036854775807) select @def='numeric('+ltrim(max(len(replace(replace(temp,'+',''),'-',''))))+',0)' from @temp where temp is not null  
115.                 else if exists (select 1 from @temp where cast(temp as numeric(38,0)) not between -2147483648 and 2147483647) set @def='bigint'  
116.                 else set @def='int'  
117.               end  
118.             else set @def='datetime'  
119.           end  
120.         else if not exists (select 1 from @temp where temp is not null and isdate(temp)=0) set @def='datetime'  
121.         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)+')'  
122.         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'  
123.         else set @def=@num  
124.         if isnumeric(@def)=1 set @def='varchar('+@def+')'  
125.         set @col=isnull(@col+',','')+'['+isnull(left(@inf,charindex(' ',@inf)-1),'C'+ltrim(@cid))+'] '+@def  
126.         select @inf=nullif(stuff(@inf,1,charindex(' ',@inf),''),''),@def=left(@def+'(',charindex('(',@def+'(')-1)  
127.         update a set code=isnull(code+',','select ')+case when @def in ('datetime','varchar','uniqueidentifier') then isnull(''''+temp+'''','null') else isnull(temp,'null') end from @code a join @temp b on a.id=b.id  
128.         delete @temp  
129.       end  
130.     if @inf is not null  
131.       begin  
132.         update @code set code=code+replicate(',null',len(@inf)-len(replace(@inf,' ',''))+1)  
133.         set @col=@col+',['+replace(rtrim(@inf),' ','] sql_variant,[')+'] sql_variant'  
134.       end  
135.     select @sql=isnull(@sql+' union all'+@ent,'')+code from @code  
136.     print '--> 测试数据：'+@tb  
137.     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  
138.     print @sql  
139.     delete @data  
140.     delete @code  
141.     select @cid=null,@spt=null,@inf=null,@col=null,@sql=null  
142.   end  
143.   
144. --> 智能代码(略)  
145. select @sql=isnull(@sql+@ent,@ent)+'select * from '+name from @tables  
146. print @sql  
147.   
148. SET NOCOUNT OFF  
149.   
150. GO  

---

加强版使用示例

[sql]  view plain copy

1. --> 多表  
2. exec cs# '@var/,  
3. id data  
4. 1, 表变量@  
5. 2, 指定逗号为列分隔符  
6. 3  空格和TAB是默认列分隔符  
7. #tmp  
8. id data  
9. -- -------  
10. 1  临时表#  
11. 2  忽略分隔横线  
12. 3  默认逗号不是列分隔符：a,b,c,d  
13. $tab  
14. id   data  
15. ---- -------  
16. 1    实体表$  
17. 2    保留null值  
18. null null'  
19. go  
20.   
21. --> 无表名/无列名(表前缀@#$前用0修饰)  
22. exec cs# '@  
23. id data  
24. 1 无表名  
25. 0#tmp  
26. 2 无列名  
27. 0$  
28. 3 双无'  
29. go  
30.   
31. --> 动态列名：列名不足动态添加Cm...Cn，数据左边对齐原则。  
32. exec cs# '@dynamic_column_name  
33. a  
34. 11 12 13  
35.    21 22  
36.       31'  
37. go  
38.   
39. --> 动态填充：数据列不足，右向填充NULL值。  
40. exec cs# '@dynamic_fill_null  
41. a  b  c  d  e  
42. 11 12 13  
43.    21 22  
44.       31'  
45. go  
46.   
47. --> 占位符：控制数据的位置，左向填充NULL值。  
48. exec cs# '@semicolon  
49. a  b  c  d  e  
50. 11 12 13  
51. ;  21 22  
52. ;  ;  31'  
53. go  
54.   
55. --> 占位符增强特性：数据中的分号不会解析为占位符。  
56. exec cs# '@semicolon  
57. a b c d  
58. ; ;; 连续;;或离散;才会被解析为占位符'  
59. go  
60.   
61. --> 时间解析1  
62. exec cs# '@time  
63. id date boy  
64. 1 2001-1-1 Mark  
65. 2 20020101 John  
66. 3 23:15:39 Paul'  
67. go  
68.   
69. --> 时间解析2  
70. exec cs# '@time  
71. id date boy  
72. 1 2001-1-1 12:28:47 Mark  
73. 2 20020101 17:30:00 John  
74. 3 2003/1/1 23:15:39 Paul'  
75. go  
76.   
77. --> 基本上可以覆盖所有的原始测试数据形式  
78. exec cs# '0@data_type  
79. 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  
80. 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'  
81. go  
82.   
83. /*  
84. PS  
85. 表前缀$和美元符号$冲突，如美元货币类型出现在第一列会报错。  
86. $99.9这种原始测试数据形式实属罕见，有需要者自行修改表前缀定义。  
87. */