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) 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 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) begin 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 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 temp like '%,%') 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 where temp 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 and 9223372036854775807) select @def='numeric('+ltrim(max(len(replace(replace(temp,'+',''),'-',''))))+',0)' from @temp where temp is not null else if exists (select 1 from @temp where cast(temp as numeric(38,0)) not between -2147483648 and 2147483647) 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') then isnull(''''+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
复杂,光看code就头晕了 友情up
declare @s varchar(8000) set @s = 'ta id col 1 1 ' exec sp_autoscript @s ,' HappyFlyStone'------------------------------------------------------------------------ -- Author: HappyFlyStone -- Date : 2009-02-10 21:33:53 -- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) -- Apr 14 2006 01:12:25 -- Copyright (c) 1988-2005 Microsoft Corporation -- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) -- -------------------------------------------------------------------------- Test Data: ta IF OBJECT_ID('ta') IS NOT NULL DROP TABLE ta Go CREATE TABLE ta(stock_his_id NVARCHAR(36),matnr_id NVARCHAR(11),qty INT,m_desc NVARCHAR(54)) Go
-- 自动生成测试数据共享加强版
--
-- 基本特性
-- * 表前缀:@表变量、#临时表、$实体表,支持混合多表。
-- * 无表名:@1...@n #1...#n T1...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),@col varchar(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
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 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)
begin
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
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 temp like '%,%') 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 where temp 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 and 9223372036854775807) select @def='numeric('+ltrim(max(len(replace(replace(temp,'+',''),'-',''))))+',0)' from @temp where temp is not null
else if exists (select 1 from @temp where cast(temp as numeric(38,0)) not between -2147483648 and 2147483647) 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') then isnull(''''+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
友情up
set @s = 'ta
id col
1 1
'
exec sp_autoscript @s ,' HappyFlyStone'------------------------------------------------------------------------
-- Author: HappyFlyStone
-- Date : 2009-02-10 21:33:53
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(stock_his_id NVARCHAR(36),matnr_id NVARCHAR(11),qty INT,m_desc NVARCHAR(54))
Go