ASE12.x以及15.x中的保留关键字

----------------------------------------------------------------------------

---- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；

---- 转载务必注明原始出处 : http://blog.csdn.net/andkylee

--- 2010-04-24 16:20:24

---- 关键字：  12.0   15.0 spt_values 关键字 reserved words sp_checkreswords

----------------------------------------------------------------------------

 

在ASE中为对象命名的时候，要避免使用系统保留关键字，以防止带来不必要的麻烦。如果是对象名称必须使用系统的自留字，请用quoted_identifer将该名称引起来。在ASE15.x中可以使用中括号[]来强制使用自留字作为对象名称。（这点功能和sqlserver相似！）

 

演示一下在ASE15.x中使用中括号引起来保留字作为列名来创建一张表。

1> select @@version 2> go ------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------- Adaptive Server Enterprise/15.0.3/EBF 16738 ESD#2/P/NT (IX86)/Windows 2003/ase1503/2708/32-bit/OPT/Mon Jul 27 20:19:56 2009 (1 row affected) 1> create table testa ([create] int ,[alter] int) 2> go 1> sp_help testa 2> go Name Owner Object_type Create_date ----- ----- ----------- ------------------- testa dbo user table Apr 24 2010 4:10PM (1 row affected) Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity ----------- ---- ------ ---- ----- ----- ------------ --------- ---------------- ---------------------- ---------- create int 4 NULL NULL 0 NULL NULL NULL NULL 0 alter int 4 NULL NULL 0 NULL NULL NULL NULL 0 Object does not have any indexes. No defined keys for this object. name type partition_type partitions partition_keys ----- ---------- -------------- ---------- -------------- testa base table roundrobin 1 NULL partition_name partition_id pages row_count segment create_date --------------- ------------ ----- --------- ------- ------------------- testa_672718418 672718418 1 0 default Apr 24 2010 4:10PM Partition_Conditions -------------------- NULL Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg) ----------- ----------- ----------- --------------------------- --------------------------- 1 1 1 1.000000 1.000000 Lock scheme Allpages The attribute 'exp_row_size' is not applicable to tables with allpages lock scheme. The attribute 'concurrency_opt_threshold' is not applicable to tables with allpages lock scheme. exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ascinserts ------------ -------------- ---------- ----------------- ------------ ----------- 1 0 0 0 0 0 (1 row affected) concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg ------------------------- --------------------- ------------------- 0 0 0 (return status = 0) 1>

 

在12.x中不支持使用中括号来限定的方法。

 

回到正题，ASE的所有保留关键字有哪些呢？

 

可以通过查询系统表来得到结果。 在spt_values表中type为W表示系统保留字。

执行select number,name from spt_values where type='W'这条语句可以得到所有的系统自留关键字。

1> select @@version 2> go ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- Adaptive Server Enterprise/12.5/SWR 9616 GA/P/NT (IX86)/OS 4.0/main/1647/32-bit / OPT/Fri Jun 01 16:58:25 2001 (1 row affected) 1> select number,name from spt_values where type='W' 2> go number name ----------- ---------------------------- 1 add 2 all 3 alter 4 and 5 any 6 arith_overflow 7 as 8 asc 9 at 10 authorization 11 avg 12 begin 13 between 14 break 15 browse 16 bulk 17 by 18 cascade 19 case 20 char_convert 21 check 22 checkpoint 23 close 24 clustered 25 coalesce 26 commit 27 compute 28 confirm 29 connect 30 constraint 31 continue 32 controlrow 33 convert 34 count 35 create 36 current 37 cursor 38 proxy_table 39 database 40 dbcc 41 deallocate 42 declare 43 default 44 delete 45 desc 46 disk 47 distinct 48 double 49 drop 50 dummy 51 dump 52 else 53 end 54 endtran 55 errlvl 56 errordata 57 errorexit 58 escape 59 except 60 exclusive 61 exec 62 execute 63 exists 64 exit 65 exp_row_size 66 external 67 fetch 68 fillfactor 69 for 70 foreign 71 from 72 goto 73 grant 74 group 75 having 76 holdlock 77 identity 78 identity_insert 79 identity_start 80 if 81 in 82 index 83 insert 84 install 85 intersect 86 into 87 is 88 isolation 89 jar 90 join 91 key 92 kill 93 level 94 like 95 lineno 96 load 97 lock 98 max 99 max_rows_per_page 100 min 101 mirror 102 mirrorexit 103 national 104 identity_gap 105 noholdlock 106 nonclustered 107 not 108 null 109 nullif 110 numeric_truncation 111 of 112 off 113 offsets 114 on 115 once 116 online 117 only 118 open 119 option 120 or 121 order 122 over 123 partition 124 perm 125 permanent 126 plan 127 precision 128 prepare 129 primary 130 print 131 privileges 132 proc 133 procedure 134 processexit 135 public 136 raiserror 137 read 138 readpast 139 readtext 140 reconfigure 141 references 142 remove 143 reorg 144 replace 145 quiesce 146 reservepagegap 147 return 148 revoke 149 role 150 rollback 151 rowcount 152 rows 153 rule 154 save 155 schema 156 select 157 set 158 setuser 159 shared 160 shutdown 161 some 162 statistics 163 stripe 164 sum 165 syb_identity 166 syb_restree 167 syb_terminate 168 table 169 temp 170 temporary 171 textsize 172 to 173 tran 174 transaction 175 trigger 176 truncate 177 tsequal 178 union 179 unique 180 unpartition 181 update 182 use 183 replication 184 user 185 user_option 186 using 187 values 188 varying 189 view 190 waitfor 191 when 192 where 193 while 194 with 195 work 196 writetext 197 modify 198 deterministic 199 func 200 function 201 inout 202 new 203 out 204 output 205 returns 206 stringsize (206 rows affected) 1>

 

 

同样在ASE15.x环境上执行同样的语句，发现ASE15.x比12.x版本多了一些保留字。

name number ---------------------------- ----------- xmltest 207 xmlextract 208 xmlparse 209 encrypt 210 decrypt 211 materialized 212 count_big 213 tracefile 214 scroll 215 decrypt_default 216 insensitive 217 semi_sensitive 218 xmltable 219

 

 

关于保留字，对应着有一个系统存储过程sp_checkreswords可以用来查看系统内所有使用保留字作为对象名称的对象。

sp_checkreswords语法为:

sp_checkreswords - Detects and displays identifiers that are Transact-SQL
         reserved words.  Checks server names, device names, database
         names, segment names, user-defined datatypes, object names,
         column names, user names, login names, and remote login names.
   sp_checkreswords [user_name_param]

 

之前我用create table testa ([create] int ,[alter] int)创建了一张表。在这张表所在的数据库内部执行sp_checkreswords过程，

系统报告出来了create  alter 这两个列名在用关键字。

1> sp_checkreswords 2> go Reserved Words Used as Database Object Names for Database,testdb. Upgrade renames sysobjects.schema to sysobjects.schemacnt. Owner ------------------------------ dbo Table Reserved Word Column Names ---------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------- ----------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------- testa alter testa create ------------------------------------------------------------- ------------------------------------------------------------- Owner ------------------------------ escourt4 Table Reserved Word Column Names ---------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------- ----------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------- syscolumn check syscolumn default ------------------------------------------------------------- ------------------------------------------------------------- Database-wide Objects --------------------- Found no reserved words used as names for database-wide objects. (return status = 4) 1>