--
数据 调用
declare @s nvarchar ( max )
set @s = N ' #T
ID NAME DTIME
1 张 2007-12-15
2 刘 2008-10-12
3 王 2009-10-13
4 赵 2009-12-15
5 孙 2009-12-17
6 于 2009-12-14
7 李 2009-12-10
8 高 2009-12-01
9 金 2009-12-10
'
exec #SQL_Script @s
if object_id ( ' Tempdb..#SQL_Script ' ) is not null
drop proc #SQL_Script
go
/* ***************************************************************************************************
%%臨時存儲過程名:#SQL_Script
%%參數:@s 分拆符串,@Flag=1 指定列名 @Flag=0 自動生成列名
%%功能:
%%備注@s格式: 空格以"~"替代,制表符以"`"替代,空值時以";"替代;用空格或制表符作為列的分隔,
連續空格、制表符只計算一個,每一行結尾用不用輸入,如果在中間位置列為null時用";"替換
%%20080527 增加了表變量,臨時表,正式表判斷
增加繁體簡體出現問號的問題。
%%20080612 增加整型準確判斷
*****************************************************************************************************
%%編寫:Ben 2008-05-19
**************************************************************************************************** */
create proc #SQL_Script( @s nvarchar ( Max ), @Flag Bit = 1 )
as
declare @s2 nvarchar ( Max ), @Tab sysname, @ColName nvarchar ( 1000 ), @Print nvarchar ( 1000 ), @TabType nvarchar ( 100 )
, @i int , @Col int , @TabID int , @IsDate Bit , @Isnumeric Bit , @Len int , @MaxLen int , @NewID Bit
, @Char Bit , @Type sysname, @IsChar Bit
select @i = charindex ( char ( 13 ) + char ( 10 ), @s ), @Tab = rtrim ( left ( @s , @i - 1 )), @s = stuff ( @s , 1 , @i + 1 , '' ),
@Tab =left ( @Tab , len ( @Tab ) - patindex ( ' %[^ ' + char ( 9 ) + char ( 32 ) + ' ]% ' , reverse ( @Tab )) + 1 ) -- 得到表名
if object_id ( ' Tempdb..# ' ) is not null
drop table #
create table #(ID int identity ,Col nvarchar ( Max ))
if right ( @s , 2 ) != char ( 13 ) + char ( 10 )
set @s = @s + char ( 13 ) + char ( 10 )
-- 替換中間空格為一個
select @i = patindex ( ' % ' + char ( 32 ) + char ( 32 ) + ' % ' , @s )
while @i > 0
select @s = replace ( @s , char ( 32 ) + char ( 32 ), char ( 32 )), @i = patindex ( ' % ' + char ( 32 ) + char ( 32 ) + ' % ' , @s )
-- 替換中間空格+制表符為一個制表符
select @i = patindex ( ' % ' + char ( 32 ) + char ( 9 ) + ' % ' , @s )
while @i > 0
select @s = replace ( @s , char ( 32 ) + char ( 9 ), char ( 9 )), @i = patindex ( ' % ' + char ( 32 ) + char ( 9 ) + ' % ' , @s )
-- 替換中間制表符+空格為一個制表符
select @i = patindex ( ' % ' + char ( 9 ) + char ( 32 ) + ' % ' , @s )
while @i > 0
select @s = replace ( @s , char ( 9 ) + char ( 32 ), char ( 9 )), @i = patindex ( ' % ' + char ( 9 ) + char ( 32 ) + ' % ' , @s )
-- 去掉每一行開始的空格
select @i = patindex ( ' % ' + char ( 13 ) + char ( 10 ) + char ( 32 ) + ' % ' , @s )
while @i > 0
select @s = replace ( @s , char ( 13 ) + char ( 10 ) + char ( 32 ), char ( 13 ) + char ( 10 )), @i = patindex ( ' % ' + char ( 13 ) + char ( 10 ) + char ( 32 ) + ' % ' , @s )
-- 去掉每一行結束的空格
select @i = patindex ( ' % ' + char ( 32 ) + char ( 13 ) + char ( 10 ) + ' % ' , @s )
while @i > 0
select @s = replace ( @s , char ( 32 ) + char ( 13 ) + char ( 10 ), char ( 13 ) + char ( 10 )), @i = patindex ( ' % ' + char ( 32 ) + char ( 13 ) + char ( 10 ) + ' % ' , @s )
-- 去掉每一行開始的制表符
select @i = patindex ( ' % ' + char ( 13 ) + char ( 10 ) + char ( 9 ) + ' % ' , @s )
while @i > 0
select @s = replace ( @s , char ( 13 ) + char ( 10 ) + char ( 9 ), char ( 13 ) + char ( 10 )), @i = patindex ( ' % ' + char ( 13 ) + char ( 10 ) + char ( 9 ) + ' % ' , @s )
-- 去掉每一行結束的制表符
select @i = patindex ( ' % ' + char ( 9 ) + char ( 13 ) + char ( 10 ) + ' % ' , @s )
while @i > 0
select @s = replace ( @s , char ( 9 ) + char ( 13 ) + char ( 10 ), char ( 13 ) + char ( 10 )), @i = patindex ( ' % ' + char ( 9 ) + char ( 13 ) + char ( 10 ) + ' % ' , @s )
-- 替換空格為制表符
select @s = replace ( @s , char ( 32 ), char ( 9 ))
-- 替換中間制表符為1個
select @i = patindex ( ' % ' + char ( 9 ) + char ( 9 ) + ' % ' , @s )
while @i > 0
select @s = replace ( @s , char ( 9 ) + char ( 9 ), char ( 9 )), @i = patindex ( ' % ' + char ( 9 ) + char ( 9 ) + ' % ' , @s )
if @Flag = 1
begin
select @i = charindex ( char ( 13 ) + char ( 10 ), @s ), @ColName = rtrim ( left ( @s , @i - 1 )) + char ( 9 ), @s = stuff ( @s , 1 , @i + 1 , '' )
while patindex ( ' %[ ' + char ( 9 ) + char ( 32 ) + ' ]% ' , @ColName ) = 1
select @ColName = stuff ( @ColName , 1 , 1 , '' )
end
select @i = charindex ( char ( 13 ) + char ( 10 ), @s ), @s = @s + char ( 13 ) + char ( 10 )
while @i > 0 and left ( @s , @i - 1 ) > ''
begin
select @s2 = quotename ( left ( @s , @i - 1 ), '''' ) , @s = stuff ( @s , 1 , charindex ( char ( 13 ) + char ( 10 ), @s ) + 1 , '' ), @i = charindex ( char ( 13 ) + char ( 10 ), @s )
set nocount on
exec ( ' insert # select N ' + @s2 )
end
-- 取列的類型
declare @T table (ID int ,Col nvarchar ( Max ))
set nocount on
insert @T select ID,Col + char ( 9 ) from #
update # set Col = null
select @Col = 0
if @Flag = 0
set @ColName = ''
while ( select max ( len (Col)) from @T ) > 0
begin
select @IsDate = min ( isdate ( replace ( replace ( left (Col, charindex ( char ( 9 ),Col) - 1 ), ' ~ ' , char ( 32 )), ' ` ' , char ( 9 )))),
@isnumeric = min ( case when charindex ( ' , ' , left (Col, charindex ( char ( 9 ),Col) - 1 )) > 0 or left (Col, charindex ( char ( 9 ),Col) - 1 ) like ' 0[^.]% ' then 0 else isnumeric ( left (Col, charindex ( char ( 9 ),Col) - 1 )) end ),
@Len = max ( Coalesce ( len ( right ( left (Col, charindex ( char ( 9 ),Col) - 1 ), charindex ( ' . ' , reverse ( left (Col, charindex ( char ( 9 ),Col) - 1 ))))), 0 )),
@MaxLen = max ( len ( left (Col, charindex ( char ( 9 ),Col) - 1 ))),
@NewID = min ( case when len ( left (Col, charindex ( char ( 9 ),Col) - 1 )) = 36 then 1 else 0 end ),
@Char = max ( case when left (Col, charindex ( char ( 9 ),Col) - 1 ) = ' . ' then 1 else 0 end ),
@Col = @Col + 1
from @T
where COl > '' and len (COl) > 1
and left (Col, charindex ( char ( 9 ),Col) - 1 ) not in ( ' ; ' , ' Null ' )
if @IsDate = 1
select @Type = ' Datetime ' , @IsChar = 1
else IF @isnumeric = 1 and @Char = 0
IF @Len > 0
select @Type = ' decimal(18, ' + rtrim ( @Len - 1 ) + ' ) ' , @IsChar = 0
else
select @Type = ' int ' , @IsChar = 0
else if @NewID = 1
select @Type = ' uniqueidentifier ' , @IsChar = 1
else
select @Type = ' nvarchar( ' + rtrim ( @MaxLen ) + ' ) ' , @IsChar = 1
update a
set Col = Coalesce (a.Col + ' , ' , '' ) + case when b.COl = '' or left (b.Col, charindex ( char ( 9 ),b.Col) - 1 ) in ( ' ; ' , ' null ' ) then ' null '
when @IsChar = 1 then case when @IsDate = 0 then ' N ' else '' end + quotename ( left (b.Col, charindex ( char ( 9 ),b.Col) - 1 ), '''' )
else left (b.Col, charindex ( char ( 9 ),b.Col) - 1 ) end
from
# a
join
@T b on a.ID = b.ID
if @Flag = 1
set @ColName = stuff ( @ColName , charindex ( char ( 9 ), @ColName ), 1 , ' ] ' + char ( 32 ) + @Type + ' ,[ ' )
else
set @ColName = @ColName + ' ,[Col ' + rtrim ( @Col ) + ' ] ' + @Type
update @T set Col = stuff (Col, 1 , charindex ( char ( 9 ),Col), '' ) where COl > '' and len (COl) > 1
end
if @Flag = 1
set @ColName = ' [ ' +left ( @ColName , len ( @ColName ) - 2 )
else
set @ColName = stuff ( @ColName , 1 , 1 , '' )
update # set Col = replace ( replace (COl, ' ~ ' , char ( 32 )), ' ` ' , char ( 9 ))
set @ColName = replace ( replace ( @ColName , ' ~ ' , char ( 32 )), ' ` ' , char ( 9 ))
select @Col = 1 , @i = max (ID) from #
print replicate ( char ( 45 ) + char ( 45 ) + char ( 62 ) + char ( 32 ), 2 ) + N ' (Ben)生成測試數據 '
print ''
if left ( @Tab , 1 ) = ' @ '
print ' declare ' + @Tab + ' table( ' + @ColName + ' ) '
else
begin
if left ( @Tab , 1 ) = ' # '
set @TabType = ' Tempdb.. '
else
set @TabType = ''
print ' if not object_id( ''' + @TabType + @Tab + ''' ) is null '
print char ( 9 ) + ' drop table ' + @Tab
print ' Go '
print ' Create table ' + @Tab + ' ( ' + @ColName + ' ) '
end
print ' Insert ' + @Tab
while exists ( select 1 from #)
begin
select @Print = ' select ' + Col + case when ID = @i then '' else ' union all ' end from # where ID = @Col
delete # where ID = @Col
print @Print
set @COl = @COl + 1
end
if left ( @Tab , 1 ) <> ' @ '
print ' Go '
else
print ' '
print ' Select * from ' + @Tab
declare @s nvarchar ( max )
set @s = N ' #T
ID NAME DTIME
1 张 2007-12-15
2 刘 2008-10-12
3 王 2009-10-13
4 赵 2009-12-15
5 孙 2009-12-17
6 于 2009-12-14
7 李 2009-12-10
8 高 2009-12-01
9 金 2009-12-10
'
exec #SQL_Script @s
if object_id ( ' Tempdb..#SQL_Script ' ) is not null
drop proc #SQL_Script
go
/* ***************************************************************************************************
%%臨時存儲過程名:#SQL_Script
%%參數:@s 分拆符串,@Flag=1 指定列名 @Flag=0 自動生成列名
%%功能:
%%備注@s格式: 空格以"~"替代,制表符以"`"替代,空值時以";"替代;用空格或制表符作為列的分隔,
連續空格、制表符只計算一個,每一行結尾用不用輸入,如果在中間位置列為null時用";"替換
%%20080527 增加了表變量,臨時表,正式表判斷
增加繁體簡體出現問號的問題。
%%20080612 增加整型準確判斷
*****************************************************************************************************
%%編寫:Ben 2008-05-19
**************************************************************************************************** */
create proc #SQL_Script( @s nvarchar ( Max ), @Flag Bit = 1 )
as
declare @s2 nvarchar ( Max ), @Tab sysname, @ColName nvarchar ( 1000 ), @Print nvarchar ( 1000 ), @TabType nvarchar ( 100 )
, @i int , @Col int , @TabID int , @IsDate Bit , @Isnumeric Bit , @Len int , @MaxLen int , @NewID Bit
, @Char Bit , @Type sysname, @IsChar Bit
select @i = charindex ( char ( 13 ) + char ( 10 ), @s ), @Tab = rtrim ( left ( @s , @i - 1 )), @s = stuff ( @s , 1 , @i + 1 , '' ),
@Tab =left ( @Tab , len ( @Tab ) - patindex ( ' %[^ ' + char ( 9 ) + char ( 32 ) + ' ]% ' , reverse ( @Tab )) + 1 ) -- 得到表名
if object_id ( ' Tempdb..# ' ) is not null
drop table #
create table #(ID int identity ,Col nvarchar ( Max ))
if right ( @s , 2 ) != char ( 13 ) + char ( 10 )
set @s = @s + char ( 13 ) + char ( 10 )
-- 替換中間空格為一個
select @i = patindex ( ' % ' + char ( 32 ) + char ( 32 ) + ' % ' , @s )
while @i > 0
select @s = replace ( @s , char ( 32 ) + char ( 32 ), char ( 32 )), @i = patindex ( ' % ' + char ( 32 ) + char ( 32 ) + ' % ' , @s )
-- 替換中間空格+制表符為一個制表符
select @i = patindex ( ' % ' + char ( 32 ) + char ( 9 ) + ' % ' , @s )
while @i > 0
select @s = replace ( @s , char ( 32 ) + char ( 9 ), char ( 9 )), @i = patindex ( ' % ' + char ( 32 ) + char ( 9 ) + ' % ' , @s )
-- 替換中間制表符+空格為一個制表符
select @i = patindex ( ' % ' + char ( 9 ) + char ( 32 ) + ' % ' , @s )
while @i > 0
select @s = replace ( @s , char ( 9 ) + char ( 32 ), char ( 9 )), @i = patindex ( ' % ' + char ( 9 ) + char ( 32 ) + ' % ' , @s )
-- 去掉每一行開始的空格
select @i = patindex ( ' % ' + char ( 13 ) + char ( 10 ) + char ( 32 ) + ' % ' , @s )
while @i > 0
select @s = replace ( @s , char ( 13 ) + char ( 10 ) + char ( 32 ), char ( 13 ) + char ( 10 )), @i = patindex ( ' % ' + char ( 13 ) + char ( 10 ) + char ( 32 ) + ' % ' , @s )
-- 去掉每一行結束的空格
select @i = patindex ( ' % ' + char ( 32 ) + char ( 13 ) + char ( 10 ) + ' % ' , @s )
while @i > 0
select @s = replace ( @s , char ( 32 ) + char ( 13 ) + char ( 10 ), char ( 13 ) + char ( 10 )), @i = patindex ( ' % ' + char ( 32 ) + char ( 13 ) + char ( 10 ) + ' % ' , @s )
-- 去掉每一行開始的制表符
select @i = patindex ( ' % ' + char ( 13 ) + char ( 10 ) + char ( 9 ) + ' % ' , @s )
while @i > 0
select @s = replace ( @s , char ( 13 ) + char ( 10 ) + char ( 9 ), char ( 13 ) + char ( 10 )), @i = patindex ( ' % ' + char ( 13 ) + char ( 10 ) + char ( 9 ) + ' % ' , @s )
-- 去掉每一行結束的制表符
select @i = patindex ( ' % ' + char ( 9 ) + char ( 13 ) + char ( 10 ) + ' % ' , @s )
while @i > 0
select @s = replace ( @s , char ( 9 ) + char ( 13 ) + char ( 10 ), char ( 13 ) + char ( 10 )), @i = patindex ( ' % ' + char ( 9 ) + char ( 13 ) + char ( 10 ) + ' % ' , @s )
-- 替換空格為制表符
select @s = replace ( @s , char ( 32 ), char ( 9 ))
-- 替換中間制表符為1個
select @i = patindex ( ' % ' + char ( 9 ) + char ( 9 ) + ' % ' , @s )
while @i > 0
select @s = replace ( @s , char ( 9 ) + char ( 9 ), char ( 9 )), @i = patindex ( ' % ' + char ( 9 ) + char ( 9 ) + ' % ' , @s )
if @Flag = 1
begin
select @i = charindex ( char ( 13 ) + char ( 10 ), @s ), @ColName = rtrim ( left ( @s , @i - 1 )) + char ( 9 ), @s = stuff ( @s , 1 , @i + 1 , '' )
while patindex ( ' %[ ' + char ( 9 ) + char ( 32 ) + ' ]% ' , @ColName ) = 1
select @ColName = stuff ( @ColName , 1 , 1 , '' )
end
select @i = charindex ( char ( 13 ) + char ( 10 ), @s ), @s = @s + char ( 13 ) + char ( 10 )
while @i > 0 and left ( @s , @i - 1 ) > ''
begin
select @s2 = quotename ( left ( @s , @i - 1 ), '''' ) , @s = stuff ( @s , 1 , charindex ( char ( 13 ) + char ( 10 ), @s ) + 1 , '' ), @i = charindex ( char ( 13 ) + char ( 10 ), @s )
set nocount on
exec ( ' insert # select N ' + @s2 )
end
-- 取列的類型
declare @T table (ID int ,Col nvarchar ( Max ))
set nocount on
insert @T select ID,Col + char ( 9 ) from #
update # set Col = null
select @Col = 0
if @Flag = 0
set @ColName = ''
while ( select max ( len (Col)) from @T ) > 0
begin
select @IsDate = min ( isdate ( replace ( replace ( left (Col, charindex ( char ( 9 ),Col) - 1 ), ' ~ ' , char ( 32 )), ' ` ' , char ( 9 )))),
@isnumeric = min ( case when charindex ( ' , ' , left (Col, charindex ( char ( 9 ),Col) - 1 )) > 0 or left (Col, charindex ( char ( 9 ),Col) - 1 ) like ' 0[^.]% ' then 0 else isnumeric ( left (Col, charindex ( char ( 9 ),Col) - 1 )) end ),
@Len = max ( Coalesce ( len ( right ( left (Col, charindex ( char ( 9 ),Col) - 1 ), charindex ( ' . ' , reverse ( left (Col, charindex ( char ( 9 ),Col) - 1 ))))), 0 )),
@MaxLen = max ( len ( left (Col, charindex ( char ( 9 ),Col) - 1 ))),
@NewID = min ( case when len ( left (Col, charindex ( char ( 9 ),Col) - 1 )) = 36 then 1 else 0 end ),
@Char = max ( case when left (Col, charindex ( char ( 9 ),Col) - 1 ) = ' . ' then 1 else 0 end ),
@Col = @Col + 1
from @T
where COl > '' and len (COl) > 1
and left (Col, charindex ( char ( 9 ),Col) - 1 ) not in ( ' ; ' , ' Null ' )
if @IsDate = 1
select @Type = ' Datetime ' , @IsChar = 1
else IF @isnumeric = 1 and @Char = 0
IF @Len > 0
select @Type = ' decimal(18, ' + rtrim ( @Len - 1 ) + ' ) ' , @IsChar = 0
else
select @Type = ' int ' , @IsChar = 0
else if @NewID = 1
select @Type = ' uniqueidentifier ' , @IsChar = 1
else
select @Type = ' nvarchar( ' + rtrim ( @MaxLen ) + ' ) ' , @IsChar = 1
update a
set Col = Coalesce (a.Col + ' , ' , '' ) + case when b.COl = '' or left (b.Col, charindex ( char ( 9 ),b.Col) - 1 ) in ( ' ; ' , ' null ' ) then ' null '
when @IsChar = 1 then case when @IsDate = 0 then ' N ' else '' end + quotename ( left (b.Col, charindex ( char ( 9 ),b.Col) - 1 ), '''' )
else left (b.Col, charindex ( char ( 9 ),b.Col) - 1 ) end
from
# a
join
@T b on a.ID = b.ID
if @Flag = 1
set @ColName = stuff ( @ColName , charindex ( char ( 9 ), @ColName ), 1 , ' ] ' + char ( 32 ) + @Type + ' ,[ ' )
else
set @ColName = @ColName + ' ,[Col ' + rtrim ( @Col ) + ' ] ' + @Type
update @T set Col = stuff (Col, 1 , charindex ( char ( 9 ),Col), '' ) where COl > '' and len (COl) > 1
end
if @Flag = 1
set @ColName = ' [ ' +left ( @ColName , len ( @ColName ) - 2 )
else
set @ColName = stuff ( @ColName , 1 , 1 , '' )
update # set Col = replace ( replace (COl, ' ~ ' , char ( 32 )), ' ` ' , char ( 9 ))
set @ColName = replace ( replace ( @ColName , ' ~ ' , char ( 32 )), ' ` ' , char ( 9 ))
select @Col = 1 , @i = max (ID) from #
print replicate ( char ( 45 ) + char ( 45 ) + char ( 62 ) + char ( 32 ), 2 ) + N ' (Ben)生成測試數據 '
print ''
if left ( @Tab , 1 ) = ' @ '
print ' declare ' + @Tab + ' table( ' + @ColName + ' ) '
else
begin
if left ( @Tab , 1 ) = ' # '
set @TabType = ' Tempdb.. '
else
set @TabType = ''
print ' if not object_id( ''' + @TabType + @Tab + ''' ) is null '
print char ( 9 ) + ' drop table ' + @Tab
print ' Go '
print ' Create table ' + @Tab + ' ( ' + @ColName + ' ) '
end
print ' Insert ' + @Tab
while exists ( select 1 from #)
begin
select @Print = ' select ' + Col + case when ID = @i then '' else ' union all ' end from # where ID = @Col
delete # where ID = @Col
print @Print
set @COl = @COl + 1
end
if left ( @Tab , 1 ) <> ' @ '
print ' Go '
else
print ' '
print ' Select * from ' + @Tab
go
本文转自曾祥展博客园博客,原文链接:http://www.cnblogs.com/zengxiangzhan/archive/2010/01/03/1638243.html,如需转载请自行联系原作者