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