检查字符串是否为 SQL Server 保留关键字 [英] Check if string is SQL Server Reserved Keywords or not

问题描述

是否可以确定任何字符串是否为 SQL Server 保留关键字?

Is it possible to determine if any string is a SQL Server Reserved keywords or not?

逻辑应该喜欢 -

IF @string is SQL Server Reserved Keywords
     RETURN 1
ELSE 
     RETURN 0

推荐答案

这是 SQL-Server 2000 的一个函数:http://www.novicksoftware.com/udfofweek/Vol2/T-SQL-UDF-Vol-2-Num-29-udf_SQL2K_IsKeywordBIT.htm

Here is a function for SQL-Server 2000: http://www.novicksoftware.com/udfofweek/Vol2/T-SQL-UDF-Vol-2-Num-29-udf_SQL2K_IsKeywordBIT.htm

这里是 SQL-Server 2012 中的关键字:http://technet.microsoft.com/en-us/library/ms189822.aspx

and here are the keywords in SQL-Server 2012: http://technet.microsoft.com/en-us/library/ms189822.aspx

因此将缺少的关键字添加到此函数中:

So add the missing keywords to this function:

CREATE  FUNCTION dbo.udf_isKeywordBIT (

    @sWord as varchar(255)
)   RETURNS BIT -- 1 when @sWord is a keyword.

AS BEGIN 
    DECLARE @sWorking varchar(255)
    DECLARE @bitRC BIT
    -- We do UPPER incase we're working in a SQL 
    SET @sWorking = UPPER(LTRIM(RTRIM(@sWord)))
    SET @bitRC = CASE WHEN @sWorking
             in ( 
'ADD', 'ALL', 'ALTER', 'AND', 'ANY', 'AS', 'ASC', 'AUTHORIZATION'
, 'BACKUP', 'BEGIN', 'BETWEEN', 'BREAK', 'BROWSE', 'BULK', 'BY'
, 'CASCADE', 'CASE', 'CHECK', 'CHECKPOINT', 'CLOSE', 'CLUSTERED'
, 'COALESCE', 'COLLATE', 'COLUMN', 'COMMIT', 'COMPUTE', 'CONSTRAINT'
, 'CONTAINS', 'CONTAINSTABLE', 'CONTINUE', 'CONVERT', 'CREATE'
, 'CROSS', 'CURRENT', 'CURRENT_DATE', 'CURRENT_TIME'
, 'CURRENT_TIMESTAMP', 'CURRENT_USER', 'CURSOR', 'DATABASE', 'DBCC'
, 'DEALLOCATE', 'DECLARE', 'DEFAULT', 'DELETE', 'DENY', 'DESC'
, 'DISK', 'DISTINCT', 'DISTRIBUTED', 'DOUBLE', 'DROP', 'DUMMY'
, 'DUMP', 'ELSE', 'END', 'ERRLVL', 'ESCAPE', 'EXCEPT', 'EXEC'
, 'EXECUTE', 'EXISTS', 'EXIT', 'FETCH', 'FILE', 'FILLFACTOR', 'FOR'
, 'FOREIGN', 'FREETEXT', 'FREETEXTTABLE', 'FROM', 'FULL', 'FUNCTION'
, 'GOTO', 'GRANT', 'GROUP', 'HAVING', 'HOLDLOCK', 'IDENTITY'
, 'IDENTITY_INSERT', 'IDENTITYCOL', 'IF', 'IN', 'INDEX', 'INNER'
, 'INSERT', 'INTERSECT', 'INTO', 'IS', 'JOIN', 'KEY', 'KILL', 'LEFT'
, 'LIKE', 'LINENO', 'LOAD', 'NATIONAL', 'NOCHECK', 'NONCLUSTERED'
, 'NOT', 'NULL', 'NULLIF', 'OF', 'OFF', 'OFFSETS', 'ON', 'OPEN'
, 'OPENDATASOURCE', 'OPENQUERY', 'OPENROWSET', 'OPENXML', 'OPTION'
, 'OR', 'ORDER', 'OUTER', 'OVER', 'PERCENT', 'PLAN', 'PRECISION'
, 'PRIMARY', 'PRINT', 'PROC', 'PROCEDURE', 'PUBLIC', 'RAISERROR'
, 'READ', 'READTEXT', 'RECONFIGURE', 'REFERENCES', 'REPLICATION'
, 'RESTORE', 'RESTRICT', 'RETURN', 'REVOKE', 'RIGHT', 'ROLLBACK'
, 'ROWCOUNT', 'ROWGUIDCOL', 'RULE', 'SAVE', 'SCHEMA', 'SELECT'
, 'SESSION_USER', 'SET', 'SETUSER', 'SHUTDOWN', 'SOME', 'STATISTICS'
, 'SYSTEM_USER', 'TABLE', 'TEXTSIZE', 'THEN', 'TO', 'TOP', 'TRANSACTION'
, 'TRIGGER', 'TRUNCATE', 'TSEQUAL', 'UNION', 'UNIQUE', 'UPDATE'
, 'UPDATETEXT', 'USE', 'USER', 'VALUES', 'VARYING', 'VIEW'
, 'WAITFOR', 'WHEN', 'WHERE', 'WHILE', 'WITH', 'WRITETEXT')
THEN 1 ELSE 0 END
    RETURN @bitRC
END

GO

这篇关于检查字符串是否为 SQL Server 保留关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！