《SYBASE中生成所有建表语句的过程》由会员分享，可在线阅读，更多相关《SYBASE中生成所有建表语句的过程（13页珍藏版）》请在金锄头文库上搜索。
1、SYBASE 中生成所有建表语句的过程Sql 代码代码:1.if exists(select 1 from sysobjects where name = sp_gent and type = P) 2. drop procedure sp_gent 3.go 4. 5.create procedure sp_gent 6.tblname varchar(30) = null, 7.prechar varchar(4) = null, -$:no print 8.table_dll varchar(16384) = null out, 9.dbname varchar(32) = null,
2、10.droptg char(1) = 1, 11.prxytx varchar(255) = null, 12.replace varchar(20) = null, 13.tabtype varchar(1) = A, -A:所有表;P:代理表;U:用户表 14.indextg varchar(3) = TPI, -T:纯表;P:主键;I:纯索引;J:除主键外的纯索引（和 TP 使用与 I 相同,和 I 同时使用 I 失效） 15.table_seg varchar(32) = null, 16.index_seg varchar(32) = null 17.as 18.begin 19.
3、 set nocount on 20. 21. if tblname is null begin 22. declare c_tblname varchar(30) 23. declare cur_1 cursor for 24. select name from sysobjects where type = U order by name 25. open cur_1 26. fetch cur_1 into c_tblname 27. while sqlstatus = 0 begin 28. exec sp_gent 29. tblname = c_tblname, 30. prech
4、ar = prechar, 31. 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_seg 39. fetch cur_1 into c_tblname 40. end 41. close cur_1 42. d
5、eallocate cursor cur_1 43. return 44. end 45. 46. declare obj_id int 47. declare sysstat2 int 48. declare username varchar(30) 49. 50. select obj_id = id, sysstat2 = sysstat2 ,username = user_name(uid) 51. from sysobjects where name = tblname and type = U 52. if rowcount 0 begin 113. if droptg $ beg
6、in 166. if prechar is not null begin 167. declare temp_dll varchar(16384),print_dll varchar(16384) 168. select temp_dll = table_dll 169. select temp_dll = prechar + temp_dll 170. while charindex(CRNW,temp_dll) 0 and char_length(temp_dll) 1 248. begin 249. print 取 default 失败%1!, def_id 250. goto err
7、251. end 252. while charindex(TAB,def_text) 0 253. select def_text = stuff(def_text,charindex(TAB,def_text),ch ar_length(TAB), ) 254. while charindex( ,def_text) 0 255. select def_text = stuff(def_text,charindex( ,def_text),char_le ngth( ), ) 256. select def_text = rtrim(ltrim(def_text) 257. 258. en
8、d 259. else 260. select def_text = null 261. 262. declare thiscol varchar(500) 263. select thiscol = 264. case when char_length(colname) $ 284. print %1!%2!,prechar, thiscol 285. 286. select table_dll = table_dll + thiscol + CRNW 287. 288. select i = i + 1 289. fetch cur_col into colname, usertype,
9、typename, length, prec,scale, def_id, nulls, ident 290. end 291. end 292. 293. if have_con = 1 and charindex(P,indextg) 0 294. begin 295. 296. select cns_name = name, status = status, indid = indid 297. from sysindexes where id = obj_id and status2 & 2 = 2 298. 299. -print exist constraint. status =
10、 %1!, status 300. 301. if indid = 1 302. select non_clu = clustered 303. else if indid 1 304. begin 305. if status & 16 = 16 306. select non_clu = clustered 307. else 308. select non_clu = nonclustered 309. end 310. 311. if status & 2048 = 2048 312. select uni_pri = primary key 313. else 314. select
11、 uni_pri = unique 315. 316. select cns_text = constraint + cns_name + + uni_pri + + non_ clu 317. 318. select i = 1, keys = 319. select thiskey = index_col(tblname, indid, i) 320. while thiskey 1 323. begin 324. select keys = keys + “, “ 325. end 326. 327. if ltrim(keys) is null 328. select keys = thiskey 329. else 330.
