SYBASE中生成所有建表语句的过程

Sql代码:

ifexists(select1fromsysobjectswherename='sp_gent'andtype='P')
dropproceduresp_gent
go

createproceduresp_gent
@tblnamevarchar(30)=null,
@precharvarchar(4)=null,--$:noprint
@table_dllvarchar(16384)=nullout,
@dbnamevarchar(32)=null,
@droptgchar(1)='1',
@prxytxvarchar(255)=null,
@replacevarchar(20)=null,
@tabtypevarchar(1)='A',--A:所有表;P:代理表;U:用户表
@indextgvarchar(3)='TPI',--T:纯表;P:主键;I:纯索引;J:除主键外的纯索引（和TP使用与I相同,和I同时使用I失效）
@table_segvarchar(32)=null,
@index_segvarchar(32)=null
as
begin
setnocounton

if@tblnameisnullbegin
declare@c_tblnamevarchar(30)
declarecur_1cursorfor
selectnamefromsysobjectswheretype='U'orderbyname
opencur_1
fetchcur_1into@c_tblname
while@@sqlstatus=0begin
execsp_gent
@tblname=@c_tblname,
@prechar=@prechar,
@dbname=@dbname,
@droptg=@droptg,
@prxytx=@prxytx,
@replace=@replace,
@tabtype=@tabtype,--A:所有表;P:代理表;U:用户表
@indextg=@indextg,--A:表和索引;T:纯表;I:纯索引
@table_seg=@table_seg,
@index_seg=@index_seg
fetchcur_1into@c_tblname
end
closecur_1
deallocatecursorcur_1
return
end

declare@obj_idint
declare@sysstat2int
declare@usernamevarchar(30)

select@obj_id=id,@sysstat2=sysstat2,@username=user_name(uid)
fromsysobjectswherename=@tblnameandtype='U'
if@@rowcount<>1
begin
print'table%1!notexists',@tblname
gotoerr
end
if@sysstat2&1024=1024begin
ifupper(@tabtype)in('U')
gotook
end
elsebegin
ifupper(@tabtype)in('P')
gotook
end

declare@colnamevarchar(30)--列名
declare@typenamevarchar(30)--类型名称
declare@usertypesmallint--类型ID
declare@lengthint--长度
declare@prectinyint--有效位数
declare@scaletinyint--精度
declare@def_idint--默认值id
declare@nullstinyint--空值
declare@identtinyint--标识列
declare@index_dllvarchar(16384)

declare@def_textvarchar(100)
declare@ide_textvarchar(30)
declare@nul_textvarchar(30)

declare@cns_textvarchar(500)
declare@uni_privarchar(40),@non_cluvarchar(40),@non_univarchar(40)

declare@lock_schemevarchar(100)

declare@keysvarchar(500),@iint
declare@thiskeyvarchar(30)
declare@sorderchar(4)
select@keys="",@i=1

declare@cns_namevarchar(30),@statusint,@indidint
declare@idx_namevarchar(50)

declare@CRNWvarchar(2)--回车换行
declare@TABchar(1)

select@CRNW=convert(varchar(2),0x0d0a)
select@TAB=convert(char(1),0x09)

declare@dbname_dotvarchar(35)
ifltrim(@dbname)isnull
select@dbname=null,@dbname_dot=null
else
select@dbname=@dbname+'.',@dbname_dot=@dbname+'..'

declare@table_namevarchar(30)
select@table_name=casewhenltrim(@replace)isnotnullthen@replaceelse@tblnameend

declare@prefix_tablevarchar(2)
select@prefix_table=casewhenltrim(@prxytx)isnotnullthen'r_'elsenullend

ifcharindex('T',@indextg)>0begin
if@droptg<>'0'
select@table_dll="ifexists(select1from"+@dbname_dot
+"sysobjectswherename='"+@prefix_table
+@table_name+"'andtype='U')"
+@CRNW+@TAB+'droptable'+@dbname+@username+'.'
+@prefix_table
+@table_name+@CRNW
+casewhen@sysstat2&1024=1024
then@TAB+'execsp_dropobjectdef'+@table_name+@CRNW
whenltrim(@prxytx)isnotnull
then@TAB+'execsp_dropobjectdefr_'+@table_name+@CRNW
elsenull
end
+'go'+@CRNW
else
select@table_dll=null

if@sysstat2&1024=1024begin
declare@OS_filevarchar(255)
select@OS_file=char_valuefromsysattributes
whereclass=9andattribute=1and
object_cinfo=@tblname
if@@rowcount=0begin
print'取代理表前缀失败%1!',@tblname
gotoerr
end
select@table_dll=@table_dll+"execsp_addobjectdef"
+@table_name
+",'"+@OS_file+"','table'"+@CRNW+
"createexistingtable"+@dbname+@username+"."
+@table_name+"("
end
elseifltrim(@prxytx)isnotnull
select@table_dll=@table_dll+"execsp_addobjectdefr_"
+@table_name+",'"+@prxytx
+@table_name+"','table'"+@CRNW
+"createexistingtable"+@dbname+@username+".r_"
+@table_name+"("
else
select@table_dll=@table_dll+'createtable'+@dbname+@username+'.'
+@table_name+'('

--如果在sybsystemprocs数据库下提交，以下注释掉

declare@tablnavarchar(255)
--select@tablna=tablnafromknp_tablwheretablcd=@tblname
--if@@rowcount=0
select@tablna=null
ifltrim(@tablna)isnotnull
select@table_dll=@table_dll+'--'+@tablna

select@prechar=casewhen@precharisnotnullthenleft(@prechar+space(4),4)else@precharend
if@prechar<>'$'begin
if@precharisnotnullbegin
declare@temp_dllvarchar(16384),@print_dllvarchar(16384)
select@temp_dll=@table_dll
select@temp_dll=@prechar+@temp_dll
whilecharindex(@CRNW,@temp_dll)>0andchar_length(@temp_dll)<>charindex(@CRNW,@temp_dll)+1begin
select@print_dll=@print_dll+left(@temp_dll,charindex(@CRNW,@temp_dll)-1)+@CRNW+@prechar
select@temp_dll=substring(@temp_dll,charindex(@CRNW,@temp_dll)+char_length(@CRNW),char_length(@temp_dll))
end
select@print_dll=@print_dll+@temp_dll
print'%1!',@print_dll
end
else
print'%1!',@table_dll
end

select@table_dll=@table_dll+@CRNW

ifltrim(@table_seg)isnullbegin
select@table_seg=s.name
fromsysobjectso,syssegmentss,sysindexesi
whereo.id=object_id(@tblname)
andi.id=o.id
andi.indid<2
andi.segment=s.segment
if@@rowcount=0begin
print'表%1!所在的段不存在',@tblname
gotoerr
end
end
end

--确定表是否有完整性约束
declare@have_conchar(1)
ifexists(select1fromsysindexeswhereid=@obj_idandstatus2&2=2)
and(ltrim(@prxytx)isnullor@sysstat2&1024=1024)
select@have_con='1'
else
select@have_con='0'


ifcharindex('T',@indextg)>0begin
declare@col_intint
select@col_int=count(*)fromsyscolumns
whereid=@obj_id

declarecur_colcursorfor
selectb.name,b.usertype,c.name,b.length,b.prec,b.scale,b.cdefault,
convert(bit,b.status&8)asNulls,
convert(bit,b.status&128)asIdent
fromsysobjectsa,syscolumnsb,systypesc
wherea.name=@tblnameanda.type='U'
anda.id=b.id
andb.usertype=c.usertype
orderbyb.colid

opencur_col
fetchcur_colinto@colname,@usertype,@typename,@length,@prec,@scale,@def_id,@nulls,@ident
while@@sqlstatus=0
begin
--系统定义的数据类型
if@usertype<100
begin

ifrtrim(@typename)in('char','varchar','nchar','nvarchar')
select@typename=@typename+'('+convert(varchar,@length)+')'
elseif@typenamein('numeric','decimal')
select@typename=@typename+'('+convert(varchar,@prec)+','+convert(varchar,@scale)+')'
elseif@typenamein('float','double')
select@typename=@typename+'('+convert(varchar,@prec)+')'
elseif@typenamein('binary','varbinary')
select@typename=@typename+'('+convert(varchar,@length)+')'
end

select@ide_text=case@identwhen1then'identity'elsenullend
select@nul_text=case@nullswhen1then'null'else'notnull'end

if@def_id>0
begin
select@def_text=ltrim(rtrim(b.text))
fromsysobjectsa,syscommentsb
wherea.id=@def_idanda.id=b.id
if@@rowcount<>1
begin
print'取default失败%1!',@def_id
gotoerr
end
whilecharindex(@TAB,@def_text)>0
select@def_text=stuff(@def_text,charindex(@TAB,@def_text),char_length(@TAB),'')
whilecharindex('',@def_text)>0
select@def_text=stuff(@def_text,charindex('',@def_text),char_length(''),'')
select@def_text=rtrim(ltrim(@def_text))

end
else
select@def_text=null

declare@thiscolvarchar(500)
select@thiscol=
casewhenchar_length(@colname)<=10thenleft(@colname+space(10),10)else@colnameend
+''+casewhenchar_length(@typename)<=15thenleft(@typename+space(15),15)else@typenameend
+''+@def_text
+''+@ide_text
+''+@nul_text

if@i=@col_intand(@have_con='0'orcharindex('P',@indextg)<=0)
select@thiscol=@thiscol+''
else
select@thiscol=@thiscol+','

--如果在sybsystemprocs数据库下提交，以下注释掉
declare@colmnavarchar(255)
select@colmna=null
--select@colmna=colmnafromknp_colmwheretablcd=@tblnameandcolmcd=@colname

ifltrim(@colmna)isnotnull
select@thiscol=@thiscol+'--'+@colmna

if@prechar<>'$'
print'%1!%2!',@prechar,@thiscol

select@table_dll=@table_dll+@thiscol+@CRNW

select@i=@i+1
fetchcur_colinto@colname,@usertype,@typename,@length,@prec,@scale,@def_id,@nulls,@ident
end
end

if@have_con='1'andcharindex('P',@indextg)>0
begin

select@cns_name=name,@status=status,@indid=indid
fromsysindexeswhereid=@obj_idandstatus2&2=2

--print'existconstraint...status=%1!',@status

if@indid=1
select@non_clu='clustered'
elseif@indid>1
begin
if@status&16=16
select@non_clu='clustered'
else
select@non_clu='nonclustered'
end

if@status&2048=2048
select@uni_pri='primarykey'
else
select@uni_pri='unique'

select@cns_text='constraint'+@cns_name+''+@uni_pri+''+@non_clu

select@i=1,@keys=''
select@thiskey=index_col(@tblname,@indid,@i)
while@thiskey<>null
begin
if@i>1
begin
select@keys=@keys+","
end

ifltrim(@keys)isnull
select@keys=@thiskey
else
select@keys=@keys+@thiskey

select@sorder=index_colorder(@tblname,@indid,@i)
if(@sorder="DESC")
select@keys=@keys+""+@sorder

select@i=@i+1
select@thiskey=index_col(@tblname,@indid,@i)
end

select@cns_text=@cns_text+'('+@keys+')'

ifltrim(@table_seg)isnullbegin
select@table_seg=s.name
fromsysobjectso,syssegmentss,sysindexesi
whereo.id=object_id(@tblname)
andi.id=o.id
andi.indid<2
andi.segment=s.segment
if@@rowcount=0begin
print'表%1!所在的段不存在',@tblname
gotoerr
end
end

ifcharindex('T',@indextg)<=0
select@cns_text='altertable'+@dbname+@username+'.'+@table_name+'add'+@cns_text+"on'"+@table_seg+"'"
if@prechar<>'$'
print'%1!%2!',@prechar,@cns_text

select@table_dll=@table_dll+@cns_text

end

ifcharindex('T',@indextg)>0begin
if@prechar<>'$'
print'%1!%2!',@prechar,')'

select@table_dll=left(@table_dll,char_length(@table_dll)-1)+@CRNW+')'

--表锁计划
if@sysstat2&8192=8192
select@lock_scheme='lockallpages'
elseif@sysstat2&16384=16384
select@lock_scheme='lockdatapages'
elseif@sysstat2&32768=32768
select@lock_scheme='lockdatarows'
select@table_dll=@table_dll+@CRNW+@lock_scheme

if@prechar<>'$'
print'%1!%2!',@prechar,@lock_scheme

select@table_seg="on'"+@table_seg+"'"
select@table_dll=@table_dll+@CRNW+@table_seg+@CRNW+'go'+@CRNW

if@prechar<>'$'begin
print'%1!%2!',@prechar,@table_seg
print'%1!go',@prechar
end
end

ifltrim(@prxytx)isnotnullor@sysstat2&1024=1024
gotook

ifcharindex('T',@indextg)>0begin
declare@part_numint,@partitionvarchar(255)
select@part_num=count(*)
fromsyspartitions
whereid=object_id(@tblname)
if@part_num<>0begin
select@partition='altertable'+@username+'.'+@table_name+'partition'+convert(varchar,@part_num)
select@table_dll=@table_dll+@CRNW+@partition
if@prechar<>'$'
print'%1!%2!',@prechar,@partition
end
end

--select@table_dllastable_dll
-------------------------------------------------------------------------------------
--检查其他索引
declare@idx_segvarchar(32)
ifcharindex('I',@indextg)>0orcharindex('J',@indextg)>0begin
ifexists(select1fromsysindexeswhereid=@obj_idandindid<>0and
(status2&2<>2orcharindex('P',@indextg)<=0andcharindex('J',@indextg)<=0))

begin
declarecur_idxcursorfor
selectname,indid,statusfromsysindexes
whereid=@obj_idandindid<>0and
(status2&2<>2orcharindex('P',@indextg)<=0andcharindex('J',@indextg)<=0)
--(status2&2<>2orcharindex('P',@indextg)<=0)
opencur_idx
fetchcur_idxinto@idx_name,@indid,@status
while@@sqlstatus=0
begin

if@indid=1
select@non_clu='clustered'
elseif@indid>1
begin
if@status&16=16
select@non_clu='clustered'
else
select@non_clu='nonclustered'
end

if@status&2=2
select@non_uni='unique'
else
select@non_uni=null

select@i=1,@keys=''
select@thiskey=index_col(@tblname,@indid,@i)
while@thiskey<>null
begin
if@i>1
begin
select@keys=@keys+","
end

ifltrim(@keys)isnull
select@keys=@thiskey
else
select@keys=@keys+@thiskey

select@sorder=index_colorder(@tblname,@indid,@i)
if@sorder="DESC"
select@keys=@keys+""+@sorder

select@i=@i+1
select@thiskey=index_col(@tblname,@indid,@i)
end

ifltrim(@index_seg)isnullbegin
select@idx_seg=s.name
fromsyssegmentss,sysindexesi
wheres.segment=i.segment
andi.id=object_id(@tblname)
andi.indid=@indid
if@@rowcount=0begin
print'索引%1!所在的段不存在',@idx_name
gotoerr
end
end
else
select@idx_seg=@index_seg

ifltrim(@keys)isnotnullbegin
declare@thisidxvarchar(500)
select@thisidx='create'+@non_uni
+@non_clu+'index'+@idx_name+'on'+@dbname+@username
+'.'+@table_name+"("+@keys+")on'"+@idx_seg+"'"

select@index_dll=@index_dll+@thisidx+@CRNW
if@prechar<>'$'
print'%1!%2!',@prechar,@thisidx
end

fetchcur_idxinto@idx_name,@indid,@status
end

ifltrim(@index_dll)isnotnullbegin
if@droptg<>'0'begin
select@index_dll=@index_dll+'go'+@CRNW
if@prechar<>'$'
print'%1!go',@prechar
end
end

select@table_dll=@table_dll+@CRNW+@index_dll
end
end
ok:
setnocountoff
return0
err:
setnocountoff
return-1
end
go

小结：不足之处当DDL脚本超过16384时，将被截断，此时需要通过ddlgen语法来生成
见我的blog：sybase
 导出DDL语句以及ddlgen的描述
http://blog.csdn.net/xujinyang/article/details/6871003