ASE12.x以及15.x中的保留关键字
在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>
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：12.0 15.0 spt_values 关键字 reserved words sp_checkreswords
————————————————————————————————-