Check if the string is SQL Server reserved keywords or not.

Question

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

The logic should like it -

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

Answer 1

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

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 

Answer 2

As far as I know, there is no built-in way. You can write your own function that will compare the string with the known reserved words from the documentation.

This list can be kept dynamic in the table so that it changes (for example, for different versions of SQL Server), the table can be updated.

In addition, this result will have one SELECT statement.

Answer 3

You can create a table containing keywords. The list can be found here: http://msdn.microsoft.com/en-us/library/ms189822.aspx

Then you can use a connection with where to determine if the string is reserved.