Solved

SQL Reserved Word list

Posted on 1998-06-12
4
830 Views
Last Modified: 2008-02-01
Is there a system table in Microsoft SQL Server 6.5 which lists all the Reserved Words?  I am writing an app which will allow the user to define the column names of a view.  I want to check to make sure that they are not trying to name a column with a reserved word like 'SELECT' or 'TABLE'.  As far as I can tell, there is no table which lists the reserved words.  I may have to create my own reference table.
0
Comment
Question by:jsullivan
  • 2
4 Comments
 
LVL 1

Expert Comment

by:vijayakumar
ID: 1091482
There is no table which lists the reserved keywords but i think its easy to build with a little bit of effort. But if you want to allow the reserved word to be either a column name or a table name, you can do that. I am givig an example here.

SET QUOTED_IDENTIFIER on
go
create table "table"
( "select"  char(40), "update" char(30) );

Whn QUOTED_IDENTIFIER s turned on, strings  with double quotation will not be evalualted or checked against keywords. But this is not a good practice and its better to avoid.

0
 

Author Comment

by:jsullivan
ID: 1091483
Thanks for your input.  I knew about the quoted identifier approach, but we did not want to use that method.
0
 
LVL 4

Accepted Solution

by:
tomook earned 100 total points
ID: 1091484
You are not the first to worry about this. Here is a list we generated for our own work. First is a list of SQL Server 6.5 key words, following is a list of new keywords in 7.
SQL 6.5:
ADD
ALL
ALTER
AND
ANY
AS
ASC
AVG
BEGIN
BETWEEN
BREAK
BROWSE
BULK
BY
CASE
CHECK
CHECKPOINT
CLOSE
CLUSTERED
COALESCE
COMMIT
COMMITTED
COMPUTE
CONFIRM
CONSTRAINT
CONTINUE
CONTROLROW
CONVERT
COUNT
CREATE
CURRENT
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_USER
CURSOR
DATABASE
DBCC
DEALLOCATE
DECLARE
DEFAULT
DELETE
DESC
DISK
DISTINCT
DOUBLE
DROP
DUMMY
DUMP
ELSE
END
ERRLVL
ERROREXIT
EXCEPT
EXEC
EXECUTE
EXISTS
EXIT
FETCH
FILLFACTOR
FLOPPY
FOR
FOREIGN
FROM
GOTO
GRANT
GROUP
HAVING
HOLDLOCK
IDENTITY
IDENTITY_INSERT
IDENTITYCOL
IF
IN
INDEX
INSENSITIVE
INSERT
INTERSECT
INTO
IS
ISOLATION
KEY
KILL
LEVEL
LIKE
LINENO
LOAD
MAX
MIN
MIRROREXIT
NOCHECK
NONCLUSTERED
NOT
NULL
NULLIF
OF
OFF
OFFSETS
ON
ONCE
ONLY
OPEN
OPTION
OR
ORDER
OVER
PERM
PERMANENT
PIPE
PLAN
PRECISION
PREPARE
PRIMARY
PRINT
PROC
PROCEDURE
PROCESSEXIT
PUBLIC
RAISERROR
READ
RECONFIGURE
REFERENCES
REPEATABLE
REPLICATION
RETURN
REVOKE
ROLLBACK
ROWCOUNT
RULE
SAVE
SCROLL
SELECT
SERIALIZABLE
SESSION_USER
SET
SETUSER
SHUTDOWN
SOME
STATISTICS
SUM
SYSTEM_USER
TABLE
TAPE
TEMP
TEMPORARY
TEXTSIZE
THEN
TO
TRAN
TRANSACTION
TRIGGER
TRUNCATE
TSEQUAL
UNCOMMITTED
UNION
UNIQUE
UPDATE
UPDATETEXT
USE
USER
VALUES
VARYING
VIEW
WAITFOR
WHEN
WHERE
WHILE
WITH
WRITETEXT

SQL 7
COLUMN
CONTAINS
LIMIT
NCHAR
NEWID
NTEXT
NVARCHAR
OPENROWSET
PIVOT
RESTORE
ROWGUID
ROWGUIDCOL
ROWS
STDEV
STDEVP
TOP
VAR
VARP

0
 

Author Comment

by:jsullivan
ID: 1091485
Thanks for the list.  I'm also going to add the list of words which SQL Server has reserved for future possible use.  These are:

 ABSOLUTE
 ACTION
 ALLOCATE
 ARE
 ASSERTION
 AT
 AUTHORIZATION
 BOTH
 CASCADE
 CASCADED
 CAST
 CATALOG
 CHAR_LENGTH
 CHARACTER
 CHARACTER_LENGTH
 COLLATE
 COLLATION
 COLUMN
 CONNECT
 CONNECTION
 CONSTRAINTS
 CORRESPONDING
 DATE
 DAY
 DEFERRABLE
 DEFERRED
 DESCRIBE
 DESCRIPTOR
 DIAGNOSTICS
 DISCONNECT
 DOMAIN
 END_EXEC
 ESCAPE
 EXCEPTION
 EXPIREDATE
 EXTERNAL
 EXTRACT
 FALSE
 FILE
 FIRST
 FULL
 GET
 GLOBAL
 HOUR
 IMMEDIATE
 INITIALLY
 INNER
 INPUT
 INTERVAL
 JOIN
 LAST
 LEADING
 LEFT
 LOCAL
 MATCH
 MINUTE
 MONTH
 NAMES
 NATIONAL
 NATURAL
 NCHAR
 NEXT
 NO
 OCTET_LENGTH
 OUTER
 OUTPUT
 OVERLAPS
 PAD
 PARTIAL
 POSITION
 PRESERVE
 PRIOR
 PRIVILEGES
 RELATIVE
 RESTRICT
 RETAINDAYS
 RIGHT
 ROWS
 SCHEMA
 SECOND
 SESSION
 SIZE
 SPACE
 SQLSTATE
 TIME
 TIMESTAMP
 TIMEZONE_HOUR
 TIMEZONE_MINUTE
 TRAILING
 TRANSLATE
 TRANSLATION
 TRUE
 UNKNOWN
 USAGE
 USING
 VALUE
 VOLUME
 WORK
 WRITE
 YEAR
 ZONE

A few of these are in the list of new words for SQL 7.

Thanks.

0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now