Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2005
    Posts
    24

    Unanswered: SQL keywords as column names

    SQL server throws an exception when I try to run the following code:

    ALTER TABLE contacts ADD default ntext NULL;

    The reason for that is that "default" is an SQL keyword.

    The way I handle this now is I put an underscore at the front:

    ALTER TABLE contacts ADD _default ntext NULL


    Is there a cleaner way to handle this, i.e. to keep the column name "default" and forse sql server to create a column with this name?

    Thank you!

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Keywords are there in a language for a reason . Why do you insist on using Default and not "DefaultValue" or "DefaultText" as the column name ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Apr 2005
    Posts
    24

    I do not really insist

    I do not really insist to use the keywords as column names. "_default" or "_defaultText" will work good enough.

    However, I have to handle these special sutuations (with using keywords) on many places and I was just wondering if there is a better way to do it.

    I just wanted to make my code cleaner.

    Thanks for the response.

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    I personally also do not prefer to use "_" in column or table names, reason being that it is a wildcard
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Apr 2005
    Posts
    24

    That is a good point.

    That is a good point. Thanks.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Enclose keyword names in square brackets:

    ALTER TABLE contacts ADD [default] ntext NULL;
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Apr 2005
    Posts
    24

    This works.

    Thanks! This also works.

    Now my concern is how to get the list of all reserved keywords.

    I found it in Books Online, but I was wondering if it is, also, in any of the system tables.

    Does anybody know?

    Thank you!

  8. #8
    Join Date
    Apr 2005
    Posts
    24

    Just a note

    Just a little note.

    The last example (with using "[]") works for column names.

    However, I run into a promblem when I used information_schema.

    There, the "[]" were omitted, i.e. my column name was inserted as "[default]" but information_schema returns "default".

    I am not sure how big overall effect this could have.

  9. #9
    Join Date
    Aug 2005
    Posts
    100
    The [] is not actually part of the name, it's used to make valid identifiers of identifiers that contain reserved words, or invalid characters, such as whitespace.

    >> I found it in Books Online, but I was wondering if it is, also, in any of the system tables
    Code:
    create function dbo.udfReservedWords()
    returns @rv table(Word varchar(17) primary key clustered) as
    /*
    Includes ODBC and Future reserved 
    select * from dbo.udfReservedWords()
    */
    begin
    insert @rv
    select 'ABSOLUTE'
    union select 'ACTION' union select 'ADA' union select 'ADD' union select 'ADMIN'
    union select 'AFTER' union select 'AGGREGATE' union select 'ALIAS' union select 'ALL'
    union select 'ALLOCATE' union select 'ALTER' union select 'AND' union select 'ANY'
    union select 'ARE' union select 'ARRAY' union select 'AS' union select 'ASC'
    union select 'ASSERTION' union select 'AT' union select 'AUTHORIZATION' union select 'AVG'
    union select 'BACKUP' union select 'BEFORE' union select 'BEGIN' union select 'BETWEEN'
    union select 'BINARY' union select 'BIT' union select 'BIT_LENGTH' union select 'BLOB'
    union select 'BOOLEAN' union select 'BOTH' union select 'BREADTH' union select 'BREAK'
    union select 'BROWSE' union select 'BULK' union select 'BY' union select 'CALL'
    union select 'CASCADE' union select 'CASCADED' union select 'CASE' union select 'CAST'
    union select 'CATALOG' union select 'CHAR' union select 'CHAR_LENGTH' union select 'CHARACTER'
    union select 'CHARACTER_LENGTH' union select 'CHECK' union select 'CHECKPOINT' union select 'CLASS'
    union select 'CLOB' union select 'CLOSE' union select 'CLUSTERED' union select 'COALESCE'
    union select 'COLLATE' union select 'COLLATION' union select 'COLUMN' union select 'COMMIT'
    union select 'COMPLETION' union select 'COMPUTE' union select 'CONNECT' union select 'CONNECTION'
    union select 'CONSTRAINT' union select 'CONSTRAINTS' union select 'CONSTRUCTOR' union select 'CONTAINS'
    union select 'CONTAINSTABLE' union select 'CONTINUE' union select 'CONVERT' union select 'CORRESPONDING'
    union select 'COUNT' union select 'CREATE' union select 'CROSS' union select 'CUBE'
    union select 'CURRENT' union select 'CURRENT_DATE' union select 'CURRENT_PATH' union select 'CURRENT_ROLE'
    union select 'CURRENT_TIME' union select 'CURRENT_TIMESTAMP' union select 'CURRENT_USER' union select 'CURSOR'
    union select 'CYCLE' union select 'DATA' union select 'DATABASE' union select 'DATE'
    union select 'DAY' union select 'DBCC' union select 'DEALLOCATE' union select 'DEC'
    union select 'DECIMAL' union select 'DECLARE' union select 'DEFAULT' union select 'DEFERRABLE'
    union select 'DEFERRED' union select 'DELETE' union select 'DENY' union select 'DEPTH'
    union select 'DEREF' union select 'DESC' union select 'DESCRIBE' union select 'DESCRIPTOR'
    union select 'DESTROY' union select 'DESTRUCTOR' union select 'DETERMINISTIC' union select 'DIAGNOSTICS'
    union select 'DICTIONARY' union select 'DISCONNECT' union select 'DISK' union select 'DISTINCT'
    union select 'DISTRIBUTED' union select 'DOMAIN' union select 'DOUBLE' union select 'DROP'
    union select 'DUMMY' union select 'DUMP' union select 'DYNAMIC' union select 'EACH'
    union select 'ELSE' union select 'END' union select 'END-EXEC' union select 'EQUALS'
    union select 'ERRLVL' union select 'ESCAPE' union select 'EVERY' union select 'EXCEPT'
    union select 'EXCEPTION' union select 'EXEC' union select 'EXECUTE' union select 'EXISTS'
    union select 'EXIT' union select 'EXTERNAL' union select 'EXTRACT' union select 'FALSE'
    union select 'FETCH' union select 'FILE' union select 'FILLFACTOR' union select 'FIRST'
    union select 'FLOAT' union select 'FOR' union select 'FOREIGN' union select 'FORTRAN'
    union select 'FOUND' union select 'FREE' union select 'FREETEXT' union select 'FREETEXTTABLE'
    union select 'FROM' union select 'FULL' union select 'FUNCTION' union select 'GENERAL'
    union select 'GET' union select 'GLOBAL' union select 'GO' union select 'GOTO'
    union select 'GRANT' union select 'GROUP' union select 'GROUPING' union select 'HAVING'
    union select 'HOLDLOCK' union select 'HOST' union select 'HOUR' union select 'IDENTITY'
    union select 'IDENTITY_INSERT' union select 'IDENTITYCOL' union select 'IF' union select 'IGNORE'
    union select 'IMMEDIATE' union select 'IN' union select 'INCLUDE' union select 'INDEX'
    union select 'INDICATOR' union select 'INITIALIZE' union select 'INITIALLY' union select 'INNER'
    union select 'INOUT' union select 'INPUT' union select 'INSENSITIVE' union select 'INSERT'
    union select 'INT' union select 'INTEGER' union select 'INTERSECT' union select 'INTERVAL'
    union select 'INTO' union select 'IS' union select 'ISOLATION' union select 'ITERATE'
    union select 'JOIN' union select 'KEY' union select 'KILL' union select 'LANGUAGE'
    union select 'LARGE' union select 'LAST' union select 'LATERAL' union select 'LEADING'
    union select 'LEFT' union select 'LESS' union select 'LEVEL' union select 'LIKE'
    union select 'LIMIT' union select 'LINENO' union select 'LOAD' union select 'LOCAL'
    union select 'LOCALTIME' union select 'LOCALTIMESTAMP' union select 'LOCATOR' union select 'LOWER'
    union select 'MAP' union select 'MATCH' union select 'MAX' union select 'MIN'
    union select 'MINUTE' union select 'MODIFIES' union select 'MODIFY' union select 'MODULE'
    union select 'MONTH' union select 'NAMES' union select 'NATIONAL' union select 'NATURAL'
    union select 'NCHAR' union select 'NCLOB' union select 'NEW' union select 'NEXT'
    union select 'NO' union select 'NOCHECK' union select 'NONCLUSTERED' union select 'NONE'
    union select 'NOT' union select 'NULL' union select 'NULLIF' union select 'NUMERIC'
    union select 'OBJECT' union select 'OCTET_LENGTH' union select 'OF' union select 'OFF'
    union select 'OFFSETS' union select 'OLD' union select 'ON' union select 'ONLY'
    union select 'OPEN' union select 'OPENDATASOURCE' union select 'OPENQUERY' union select 'OPENROWSET'
    union select 'OPENXML' union select 'OPERATION' union select 'OPTION' union select 'OR'
    union select 'ORDER' union select 'ORDINALITY' union select 'OUT' union select 'OUTER'
    union select 'OUTPUT' union select 'OVER' union select 'OVERLAPS' union select 'PAD'
    union select 'PARAMETER' union select 'PARAMETERS' union select 'PARTIAL' union select 'PASCAL'
    union select 'PATH' union select 'PERCENT' union select 'PLAN' union select 'POSITION'
    union select 'POSTFIX' union select 'PRECISION' union select 'PREFIX' union select 'PREORDER'
    union select 'PREPARE' union select 'PRESERVE' union select 'PRIMARY' union select 'PRINT'
    union select 'PRIOR' union select 'PRIVILEGES' union select 'PROC' union select 'PROCEDURE'
    union select 'PUBLIC' union select 'RAISERROR' union select 'READ' union select 'READS'
    union select 'READTEXT' union select 'REAL' union select 'RECONFIGURE' union select 'RECURSIVE'
    union select 'REF' union select 'REFERENCES' union select 'REFERENCING' union select 'RELATIVE'
    union select 'REPLICATION' union select 'RESTORE' union select 'RESTRICT' union select 'RESULT'
    union select 'RETURN' union select 'RETURNS' union select 'REVOKE' union select 'RIGHT'
    union select 'ROLE' union select 'ROLLBACK' union select 'ROLLUP' union select 'ROUTINE'
    union select 'ROW' union select 'ROWCOUNT' union select 'ROWGUIDCOL' union select 'ROWS'
    union select 'RULE' union select 'SAVE' union select 'SAVEPOINT' union select 'SCHEMA'
    union select 'SCOPE' union select 'SCROLL' union select 'SEARCH' union select 'SECOND'
    union select 'SECTION' union select 'SELECT' union select 'SEQUENCE' union select 'SESSION'
    union select 'SESSION_USER' union select 'SET' union select 'SETS' union select 'SETUSER'
    union select 'SHUTDOWN' union select 'SIZE' union select 'SMALLINT' union select 'SOME'
    union select 'SPACE' union select 'SPECIFIC' union select 'SPECIFICTYPE' union select 'SQL'
    union select 'SQLCA' union select 'SQLCODE' union select 'SQLERROR' union select 'SQLEXCEPTION'
    union select 'SQLSTATE' union select 'SQLWARNING' union select 'START' union select 'STATE'
    union select 'STATEMENT' union select 'STATIC' union select 'STATISTICS' union select 'STRUCTURE'
    union select 'SUBSTRING' union select 'SUM' union select 'SYSTEM_USER' union select 'TABLE'
    union select 'TEMPORARY' union select 'TERMINATE' union select 'TEXTSIZE' union select 'THAN'
    union select 'THEN' union select 'TIME' union select 'TIMESTAMP' union select 'TIMEZONE_HOUR'
    union select 'TIMEZONE_MINUTE' union select 'TO' union select 'TOP' union select 'TRAILING'
    union select 'TRAN' union select 'TRANSACTION' union select 'TRANSLATE' union select 'TRANSLATION'
    union select 'TREAT' union select 'TRIGGER' union select 'TRIM' union select 'TRUE'
    union select 'TRUNCATE' union select 'TSEQUAL' union select 'UNDER' union select 'UNION'
    union select 'UNIQUE' union select 'UNKNOWN' union select 'UNNEST' union select 'UPDATE'
    union select 'UPDATETEXT' union select 'UPPER' union select 'USAGE' union select 'USE'
    union select 'USER' union select 'USING' union select 'WAITFOR' union select 'VALUE'
    union select 'VALUES' union select 'VARCHAR' union select 'VARIABLE' union select 'VARYING'
    union select 'WHEN' union select 'WHENEVER' union select 'WHERE' union select 'WHILE'
    union select 'VIEW' union select 'WITH' union select 'WITHOUT' union select 'WORK'
    union select 'WRITE' union select 'WRITETEXT' union select 'YEAR' union select 'ZONE'
    return
    end

  10. #10
    Join Date
    Aug 2005
    Posts
    100
    PLEASE NOTE:
    If you use the above udf, replace all the:
    "union select" to "union all select"

    I removed the "all", 'coz the text was to long for dbforums.

    rockmoose

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •