SYBASE中生成所有建表語(yǔ)句的過(guò)程

1、SYBASE中生成所有建表語(yǔ)句的過(guò)程SYBASE 中生成所有建表語(yǔ)句的過(guò)程Sql 代碼 :1. if exists(select 1 from sysobjects where name = sp_gent and type = P)2. drop procedure sp_gent3. go4.5. create procedure sp_gent6. tblname varchar(30) = null ,7. prechar varchar(4) = null , -$:no prin t8. table_dll varchar(16384) = null out,9. dbname v

2、archar(32) = null ,10. droptg char(1) = 1,11. prxytx varchar(255) = null ,12. replace varchar(20) = null ,13. tabtype varchar(1) = A, -A: 所有表 ;P: 代理表 ;U: 用戶表4. e1.14. indextg varchar(3) = TPI , -T: 純表 ;P: 主鍵;I:純索引;J: 除主鍵外的純索引(和 TP 使用與 I 相同,和 I 同時(shí)使用 I 失效

3、)table_seg varchar(32) =null ,index_seg varchar(32) = null as beginset nocount onif tblname is null begindeclare c_tblname varchar(30)declare cur_1 cursor forselect name from sysobjects wher type = U order by nameopen cur_1fetch cur_1 into c_tblname while sqlstatus = 0 begin exec sp_genttblname = c_

4、tblname, prechar = prechar, dbname = dbname ,32.droptg = droptg ,33.prxytx = prxytx ,34. replace = replace ,35.tabtype = tabtype, -A:所有表 ;P: 代理表 ;U: 用戶表36.indextg = indextg, -A:表和索引 ;T:純表;I:純索引37.table_seg = table_seg,38.index_seg = index_seg39.fetch cur_1 into c_tblname40.end41.close cur_142.deallo

5、cate cursor cur_143.return44.end45.46.declare obj_id int47.declare sysstat2 int48.declare username varchar(30)49.50.select obj_id = id, sysstat2 = sysstat2 ,username = user_name(uid)51. from sysobjects where name = tblname and type = U52. if rowcount 153. begin54. print table %1! not exists , tblna

6、me55. goto err56. end57. if sysstat2 & 1024 = 1024 begin58. if upper (tabtype) in (U)59. goto ok60. end61. else begin62. if upper (tabtype) in (P)63. goto ok64. end65.66. declare colname varchar(30) - 列名67. declare typename varchar(30) - -類型名稱68.declare usertype smallint- 類型ID69.declare length int -

7、長(zhǎng)度70.declare prec tinyint -有效位數(shù)71.declare scale tinyint -精度72.declare def_id int-默認(rèn)值id73.declare nulls tinyint -空值74.declare ident tinyint -標(biāo)識(shí)列75.declare index_dll varchar(16384)76.77.declare def_text varchar(100)78.declare ide_text varchar(30)79.declare nul_text varchar(30)80.81.declare cns_text va

8、rchar(500)82. declare uni_pri varchar(40), non _clu varchar(40), non_uni varchar(40)83.84. declare lock_scheme varchar(100)85.86. declare keys varchar(500), i int87. declare thiskey varchar(30)88. declare sorder char(4)89. select keys = , i = 190.91. declare cns_name varchar(30), s tatus int, indid

9、int92. declare idx_name varchar(50)93.94. declare CRNW varchar(2) - 回車 換行95. declare TAB char(1)96.97. select CRNW = convert (varchar(2), 0x0d0a)98. select TAB = convert (char(1), 0x09)99.100. declare dbname_dot varchar(35)101. if ltrim(dbname) is null102. select dbname = null ,dbnam e_dot = null103

10、. else104. select dbname = dbname +. ,dbname_dot = dbname + .105.106. declare table_name varchar(30)107. select table_name = case when l trim( replace ) is not null then replace else tblname end108.109. declare prefix_table varchar(2)110.select prefix_table = case when ltrim(prxytx) is not null then

11、 r_ else nullend111.112.if charindex( T ,indextg) 0 begi113.if droptg 0114.select table_dll = if exists(select 1 from +dbname_dot115.+ sysobjects where name = +prefix_table116.+table_name+ and type = U)117.+CRNW+TAB+ drop table +dbname+username + .118.+prefix_table119.+table_name+CRNW120.+ case when

12、 sysstat2 & 1024 =1024121.then TAB+ exec sp_dropobjectdef +table_name+CRNW122.when ltrim(prxytx) is notnull123.then TAB+exec sp_dropobjectdef r_ +table_name+CRNW124.else null125.end126.+ go +CRNW127.else128.select table_dll= null129.130.if sysstat2 & 1024= 1024 begi131. )declare OS_file varchar(255)

13、132.select OS_file =char_valuefrom sysattributes133.where class9 and attribute =1 and134.object_cinfo= tblname135.if rowcount =0 begin136.print 取代理表前綴失敗%1! ,tblname137.goto err138.end139.select table_dll = table_dll+ exec sp_addobjectdef 140.+table_name141.+ , +OS_file+ , table +CRNW+142.create exis

14、ting table + dbname+username + .143.+table_name + (144.end145.else if ltrim(prxytx) is not null146.select table_dll = table_dll+ execsp_addobjectdef r_147.+table_name+ , +prxytx148.+table_name+ , table +CRNW149.+ create existing table + dbname+username + .r_150.+table_name + (151.else152.select tabl

15、e_dll = table_dll+ create table + dbname+username + .153.+table_name + (154.155.- 如果在 sybsystemprocs 數(shù)據(jù)庫(kù)下提交，以下注釋掉156.157.declare tablna varchar(255)158.-select tablna = tablna fromknp_tabl where tablcd = tblname159.-if rowcount = 0160.select tablna = null161.if ltrim(tablna) is not null162.select ta

16、ble_dll = table_dll + - +tablna163.164. select prechar = case when prechar is not null then left (prechar+ s pace (4),4) else prechar end165.166.if prechar $ beginif prechar is not null begin167. declare temp_dll varchar(16384),print_dll varchar(16384)168. _dll169.select temp_dll = tableselect temp_

17、dll = prechar + temp_dll170.while charindex(CRNW,temp_dll) 0 and char_length(temp _dll) charindex(CRNW,temp_dll)+ 1 begin171.select print_dll = print_dll + left (temp_dll,charindex(CRNW,temp_dll) - 1) + CRNW+prechar172. select temp_dll = subst ring (temp_dll,charindex(CRNW,temp_dll)+char_length(CRNW

18、),char_length(temp_dll)173. end174. select print_dll = print_dll + temp_dll175.print%1! ,print_dll176.end177.else178.print%1! ,table_dll179.end180.181.select table_dll = table_dll +CRNW182.183. if ltrim(table_seg) isnull begin184. select table_seg = 185. from sysobjects o, syssegments s, sysin

19、dexes i186. where o.id = object_id(tblname)187.and i.id = o.id188.and i.indid 0 begi n207. declare col_int int208. select col_int = count (*) fromsyscolumns209. where id = obj_id210.211. declare cur_col cursor for212. select , b.usertype, , b.length, b.prec, b.scale, b.cdefaul t,213. co

20、nvert (bit,b.status&8) as Nulls,214. convert (bit,b.status&128)as Ident215. from sysobjects a, syscolu mns b, systypes c216. where = tblnameand a.type = U217. and a.id = b.id218.and b.usertype =c.usertype219.order by b.colid220.221.open cur_col222.fetch cur_col into colname, usertype, typenam

21、e, length, prec,scale, def_id, nulls, ident223.while sqlstatus = 0224.begin225.- 系統(tǒng)定義的數(shù)據(jù)類型226.if usertype 0243.begin244.select def_text = ltrim(rtrim(b.text)245.from sysobjects a, syscomments b246.where a.id = def_idand a.id =b.id247.if rowcount 1248.begin249.print 取 default 失敗%1! , def_id250.goto e

22、rr251.end252.while charindex(TAB,def_text) 0253.select def_text = stuff(def_text,charindex(TAB,def_text),char_length(TAB), )254.while charindex( ,def_text) 0255.select def_text = stuff(def_text,charindex( ,def_text),char_length( ), )256.select def_text = rtrim(ltrim(def_text)257.258.end259.else260.s

23、elect def_text = null261.262.declare thiscol varchar(500)263.select thiscol =264.case when char_length(colname) = 10 then left (colname+ space (10),10) else colname end265.+ + case when char_length(typename) = 15 then left (typename+ space (15),15) else typename end266.+ + def_text267.+ + ide_text26

24、8. + + nul_text269.270. if i = col_int and (have_con = 0 or charindex( P ,indextg) = 0 )271. select thiscol = thiscol+ 272. else273. select thiscol = thiscol+ ,274.275. - 如果在 sybsystemprocs 數(shù)據(jù) 庫(kù)下提交，以下注釋掉276. declare colmna varchar(255)277. select colmna = null278. -select colmna = colmna fr279.280.o

25、m knp_colm where tablcd = tblname and colmcd = colnameif ltrim(colmna) is not null281.select thiscol= thiscol+ - +colmna282.283.if prechar $284.print %1!%2!,prechar, thiscol285.286.select table_dll =table_dll +thiscol + CRNW287.288.select i = i + 1289.fetch cur_col into colname,usertype, typename, l

26、ength, prec, scale, def_id, nulls, ident290.end291.end292.293.if have_con = 1 and charindex( P ,indextg) 0294.begin295.296.select cns_name = name, status =status, indid = indid297.from sysindexes where id =obj_id and status2 & 2 = 2298.299.-print exist constraint.status= %1!, status300.301.if indid

27、= 1302.select non_clu =clustered303.else if indid 1304.begin305.if status & 16 = 16306.select non_clu =clustered307.else308.select non_clu =nonclustered309.end310.311.if status & 2048 = 2048312.select uni_pri =primary key313.else314.select uni_pri =unique315.316.select cns_text =constraint + cns_nam

28、e + + uni_pri + + non_clu317.318.select i = 1, keys = 319.select thiskey = index_col(tblname, indid, i)320.while thiskey null321.begin322.if i 1323.begin324.select keys =keys + ,325.end326.327.if ltrim(keys) isnull328.select keys =thiskey329.else330.select keys = keys + thiskey331.332.select sorder

29、= index_colorder(tblname, indid, i)333.if (sorder = DESC )334.select keys = keys + +sorder335.336.select i = i + 1337.select thiskey = index_col(tblname, indid, i)338.end339.340.select cns_text = cns_text + (+ keys + )341.342.if ltrim(table_seg) is null begi343.select table_seg = 344.from syso

30、bjects o, syssegments s,sysindexes i345.where o.id = object_id(tblname)346.and i.id = o.id347.and i.indid 2348.and i.segment = s.segment349.if rowcount = 0 begin350.print 表%1! 所在的段不存在 ,tblname351.goto err352.end353.end354.355.if charindex( T ,indextg) = 0356.select cns_text = alter table +dbname+use

31、rname + . +table_name+ add +cns_text+ on + table_seg +357.if prechar $358.print %1!%2! ,prechar,cns_text359.360.select table_dll = table_dll +cns_text361.362.end363.364.if charindex( T ,indextg) 0 begi365.if prechar $366.print %1!%2! ,prechar, ) 367.368.select table_dll = left (table_dll,char_length

32、(table_dll)-1) + CRNW + )369.370.- 表鎖計(jì)劃371.if sysstat2 & 8192 = 8192372.select lock_scheme = lockallpages373. else if sysstat2 & 16384 = 16384374. select lock_scheme =lockdatapages375. else if sysstat2 & 32768 = 32768376. select lock_scheme =lockdatarows377. select table_dll = table_dll +CRNW + lock

33、_scheme378.379.if prechar $380. print %1!%2! ,prechar, lo ck_scheme381.382.select table_seg = on + table_seg+ 383.select table_dll = table_dll +CRNW + table_seg + CRNW+ go + CRNW384.385.if prechar $ begin386. print %1!%2! ,prechar, ta ble_seg387.print %1!go,prechar388.end389.end390.391.if ltrim(prxy

34、tx) isnot null or sysstat2 & 1024 = 1024392. goto ok393.394. if charindex( T ,indextg) 0 begi n395. declare part_num int,partition varchar(255)396. select part_num = count (*)397. from syspartitions398. where id = object_id(tblname)399. if part_num 0 begin400. select partition = alter table+ usernam

35、e + . + table_name + p artition +convert (varchar,part_num)401.select table_dll = table_dll +CRNW + partition402.if prechar $403.print %1!%2! ,prechar, partition404.end405.end406.407.-select table_dll as table_dll408.409.- 檢查其他索引410.declare idx_seg varchar(32)411.if charindex( I ,indextg) 0 or chari

36、ndex( J ,indextg) 0 begin412.if exists (select 1 from sysindexeswhere id = obj_id and indid 0 and413.(status2 & 2 2 or charindex(P ,indextg) = 0 and charindex( J ,indextg) = 0)414.415.begin416.declare cur_idx cursor for417.select name, indid, status from sysindexes418.where id = obj_idand indid 0 an

37、d419.(status2 & 2 2 or charindex(P ,indextg) = 0 and charindex(J ,indextg)= 0)420. -(status2 & 2 2 or charindex(P,indextg) 1429.begin430.if status & 16 = 16431.select non_clu = clustered432.else433.select non_clu = nonclustered434.end435.436.if status & 2 = 2437.select non_uni = unique 438.else439.s

38、elect non_uni = null440.441.select i = 1,keys = 442.select thiskey = index_col(tblname, indid, i)443.while thiskey null444.begin445.if i 1446.begin447.select keys= keys448.end449.450.if ltrim(keys) isnull451.select keys= thiskey452.else453.select keys= keys+ thiskey454.455.select sorder =index_colorder(tblname, indid, i)456.if sorder = DESC457.select keys=