将一个表转化成建表语句的存储过程代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】将一个表转化成建表语句的存储过程

作者:lihan6415151528 / 发布于2014/2/8/ 597

将一个表转化成建表语句的存储过程

SETQUOTED_IDENTIFIERON
GO
SETANSI_NULLSON
GO

CREATEprocpGetInsertSQL (@TableNamevarchar(256))
as
begin
setnocounton
declare@sqlstrvarchar(4000)
declare@sqlstr1varchar(4000)
declare@sqlstr2varchar(4000)
select@sqlstr='select''insert'+@tablename
select@sqlstr1=''
select@sqlstr2='('
select@sqlstr1='values (''+'
select@sqlstr1=@sqlstr1+col+'+'',''+',@sqlstr2=@sqlstr2+name+','from(selectcase
-- when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
whena.xtype=104then'case when'+a.name+'is null then''NULL''else'+'convert(varchar(1),'+a.name+')'+'end'
whena.xtype=175then'case when'+a.name+'is null then''NULL''else'+'''''''''+'+'replace('+a.name+','''''''','''''''''''')'+'+'''''''''+'end'
whena.xtype=61then'case when'+a.name+'is null then''NULL''else'+'''''''''+'+'convert(varchar(23),'+a.name+',121)'+'+'''''''''+'end'
whena.xtype=106then'case when'+a.name+'is null then''NULL''else'+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name+')'+'end'
whena.xtype=62then'case when'+a.name+'is null then''NULL''else'+'convert(varchar(23),'+a.name+',2)'+'end'
whena.xtype=56then'case when'+a.name+'is null then''NULL''else'+'convert(varchar(11),'+a.name+')'+'end'
whena.xtype=60then'case when'+a.name+'is null then''NULL''else'+'convert(varchar(22),'+a.name+')'+'end'
whena.xtype=239then'case when'+a.name+'is null then''NULL''else'+'''''''''+'+'replace('+a.name+','''''''','''''''''''')'+'+'''''''''+'end'
whena.xtype=108then'case when'+a.name+'is null then''NULL''else'+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name+')'+'end'
whena.xtype=231then'case when'+a.name+'is null then''NULL''else'+'''''''''+'+'replace('+a.name+','''''''','''''''''''')'+'+'''''''''+'end'
whena.xtype=59then'case when'+a.name+'is null then''NULL''else'+'convert(varchar(23),'+a.name+',2)'+'end'
whena.xtype=58then'case when'+a.name+'is null then''NULL''else'+'''''''''+'+'convert(varchar(23),'+a.name+',121)'+'+'''''''''+'end'
whena.xtype=52then'case when'+a.name+'is null then''NULL''else'+'convert(varchar(12),'+a.name+')'+'end'
whena.xtype=122then'case when'+a.name+'is null then''NULL''else'+'convert(varchar(22),'+a.name+')'+'end'
whena.xtype=48then'case when'+a.name+'is null then''NULL''else'+'convert(varchar(6),'+a.name+')'+'end'
-- when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
whena.xtype=167then'case when'+a.name+'is null then''NULL''else'+'''''''''+'+'replace('+a.name+','''''''','''''''''''')'+'+'''''''''+'end'
else'''NULL'''
endascol,a.colid,a.name
fromsyscolumns awherea.id=object_id(@tablename)anda.xtype<>189anda.xtype<>34anda.xtype<>35and a.xtype<>36
 )torderbycolid

select@sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+')'+left(@sqlstr1,len(@sqlstr1)-3)+')''from'+@tablename
-- print @sqlstr
exec(@sqlstr)
setnocountoff
end

GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO


createtabletest(namevarchar(20))
inserttest
select'1'unionall
select'2'unionall
select'3'unionall
select'4'
go

execpGetInsertSQL'test'

-------------------------------------------------
inserttest (name)values('1')
inserttest (name)values('2')
inserttest (name)values('3')
inserttest (name)values('4')

试试其它关键字

　表转化成建表语句　

同语言下

可能有用的

lihan6415151528贡献的其它代码(3)