December 3, 2002 at 11:49 am
I have a module which allows dynamic creation of tables within the database. I need to prohibit use of reserved words for column names. Is there any way of programatically accessing the reserved word list.
December 3, 2002 at 3:56 pm
To the best of my knowledge, no. You can use systypes in master to give you a list of the datetype names but no list for things such as function names and keywords like from, where, and select.
December 3, 2002 at 3:57 pm
You could load it up.
Alternatively, enclose all table and field names in [], which will allow you to use reserved words.
Steve Jones
December 4, 2002 at 3:51 am
Not that I would recommend it, but I guess it could be done by creating a proc that accepts the word to check as a parameter, and tries running something like this:
DECLARE @sql varchar(1000)
SET @sql = 'SELECT 1 AS ' + @word
EXEC (@sql)
If an error is produced, then the word is reserved. You would of course need to watch out for injection and stuff, but I'm just showing it could be done. Another alternative could be to create a temporary table with the columns that the user is trying to create, and then just drop it again if all was OK. If not OK, then you would once again know that they did sometomething wrong in their column declaration. But I would definately recommend storing the reserved words in a table and checking for their existance there instead though.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
December 5, 2002 at 10:14 am
heh, you guys overlooked the best and most obvious resource! Lookup Reserved Keywords in SQL BOL and you'll find everything you're looking for, and then some.. including ODBC Reserved Keywords and even Future Reserved Keywords..
ADD EXCEPT PERCENT ALL EXEC PLAN ALTER EXECUTE PRECISION AND EXISTS PRIMARY ANY EXIT PRINT AS FETCH PROC ASC FILE PROCEDURE AUTHORIZATION FILLFACTOR PUBLIC BACKUP FOR RAISERROR BEGIN FOREIGN READ BETWEEN FREETEXT READTEXT BREAK FREETEXTTABLE RECONFIGURE BROWSE FROM REFERENCES BULK FULL REPLICATION
BY FUNCTION RESTORE CASCADE GOTO RESTRICT CASE GRANT RETURN CHECK GROUP REVOKE
CHECKPOINT HAVING RIGHT CLOSE HOLDLOCK ROLLBACK CLUSTERED IDENTITY ROWCOUNT
COALESCE IDENTITY_INSERT ROWGUIDCOL COLLATE IDENTITYCOL RULE COLUMN IF SAVE
COMMIT IN SCHEMA COMPUTE INDEX SELECT CONSTRAINT INNER SESSION_USER CONTAINS INSERT SET CONTAINSTABLE INTERSECT SETUSER CONTINUE INTO SHUTDOWN CONVERT IS SOME CREATE JOIN STATISTICS CROSS KEY SYSTEM_USER CURRENT KILL TABLE CURRENT_DATE LEFT TEXTSIZE CURRENT_TIME LIKE THEN CURRENT_TIMESTAMP LINENO TO
CURRENT_USER LOAD TOP CURSOR NATIONAL TRAN DATABASE NOCHECK TRANSACTION
DBCC NONCLUSTERED TRIGGER DEALLOCATE NOT TRUNCATE DECLARE NULL TSEQUAL DEFAULT NULLIF UNION DELETE OF UNIQUE DENY OFF UPDATE DESC OFFSETS UPDATETEXT DISK ON USE DISTINCT OPEN USER DISTRIBUTED OPENDATASOURCE VALUES DOUBLE OPENQUERY ARYING DROP OPENROWSET VIEW DUMMY OPENXML WAITFOR DUMP OPTION WHEN ELSE OR WHERE END ORDER WHILE ERRLVL OUTER WITH ESCAPE OVER WRITETEXT
-Ken
December 5, 2002 at 11:07 am
There's not a function available without rolling your own anyway so take the word list above and create a simple table that accepts your words as input.
You also can use square brackets around EVERYTHING and ignore the problem (if you can call that ignoring).
December 5, 2002 at 3:36 pm
Even with the reserved keyword list it may help to go ahead and wrap in brackets. Future upgrades may see additional keywords and they may exist in you database. Then you also have the future reservered, odbc reserved and embeded sql reserved lists to look at (I suggest use 2000 BOL to build the list).
December 5, 2002 at 3:51 pm
My thanks to all for the responses. This is the first question I have posted and I was impressed by both quantity and quality of responses. My final short-term answer was to simple prefix any column name with something which could not conflict with reserved words. Longer term I will be implementing a design which removes the requirement for this dynamic creation in the first place. Thanks againg
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply