Avoid reserved words in Sql Statements
Sql Server allows the developer to create a script using reserved words as column names. It is possible to do this by adding Square brackets around the field definition []. Whilst this is a simple solution it means that every other script has to follow the same convention. A Better way to avoid this problem is to check the Sql script before you run it into the database.
I have created a script that will check a Sql statement against a list of reserved words. It will then output the results in LINQPad and allows the developer to check their code for reserved words.
The reserved words come from this article : Microsoft Docs TSQL Reserved Keywords
The Script
This script has been designed to run in LINQPad. The script does the following
- Gets the text from the Windows Clipboard
- Builds a List of Reserved Words
- Compares Text against the reserved words list and prints a list of results to the LINQPad output window
using System.Collections.Generic;
using System.Linq;
using System.Windows;
void Main()
{
var reservedWords = GetReservedWords();
var candidate = GetClipboardTextAsList();
var results = OutputReservedWords(reservedWords,candidate.Words);
results.Dump();
candidate.Original.Dump();
}
private (string Original, List<string> Words) GetClipboardTextAsList()
{
var txt = Clipboard.GetText();
var words = txt?.Split(" ").Where(c=> !string.IsNullOrWhiteSpace(c)).Select(c=> c.Trim()).ToList();
return (txt,words);
}
private List<string> GetReservedWords()
{
var sqlServerReservedWords = new List<string> { "ADD", "EXTERNAL", "PROCEDURE ",
"ALL", "FETCH", "PUBLIC ", "ALTER", "FILE",
"RAISERROR ", "AND", "FILLFACTOR", "READ ", "ANY",
"FOR", "READTEXT ", "AS", "FOREIGN", "RECONFIGURE ",
"ASC", "FREETEXT", "REFERENCES ", "AUTHORIZATION",
"FREETEXTTABLE", "REPLICATION ", "BACKUP", "FROM",
"RESTORE ", "BEGIN", "FULL", "RESTRICT ",
"BETWEEN", "FUNCTION", "RETURN ", "BREAK",
"GOTO", "REVERT ", "BROWSE", "GRANT", "REVOKE",
"BULK", "GROUP", "RIGHT ", "BY",
"HAVING", "ROLLBACK ", "CASCADE", "HOLDLOCK",
"ROWCOUNT ", "CASE", "IDENTITY", "ROWGUIDCOL ",
"CHECK", "IDENTITY_INSERT", "RULE ", "CHECKPOINT",
"IDENTITYCOL", "SAVE ", "CLOSE", "IF", "SCHEMA",
"CLUSTERED", "IN", "SECURITYAUDIT ", "COALESCE",
"INDEX", "SELECT ", "COLLATE", "INNER",
"SEMANTICKEYPHRASETABLE ", "COLUMN", "INSERT",
"SEMANTICSIMILARITYDETAILSTABLE ", "COMMIT", "INTERSECT",
"SEMANTICSIMILARITYTABLE ", "COMPUTE", "INTO", "SESSION_USER",
"CONSTRAINT", "IS", "SET ", "CONTAINS",
"JOIN", "SETUSER ", "CONTAINSTABLE", "KEY",
"SHUTDOWN ", "CONTINUE", "KILL", "SOME ",
"CONVERT", "LEFT", "STATISTICS ", "CREATE",
"LIKE", "SYSTEM_USER ", "CROSS", "LINENO",
"TABLE ", "CURRENT", "LOAD", "TABLESAMPLE ",
"CURRENT_DATE", "MERGE", "TEXTSIZE ", "CURRENT_TIME",
"NATIONAL", "THEN ", "CURRENT_TIMESTAMP", "NOCHECK",
"TO ", "CURRENT_USER", "NONCLUSTERED", "TOP ",
"CURSOR", "NOT", "TRAN ", "DATABASE", "NULL",
"TRANSACTION ", "DBCC", "NULLIF", "TRIGGER ",
"DEALLOCATE", "OF", "TRUNCATE ", "DECLARE",
"OFF", "TRY_CONVERT ", "DEFAULT", "OFFSETS",
"TSEQUAL ", "DELETE", "ON", "UNION ", "DENY",
"OPEN", "UNIQUE ", "DESC", "OPENDATASOURCE",
"UNPIVOT ", "DISK", "OPENQUERY", "UPDATE ",
"DISTINCT", "OPENROWSET", "UPDATETEXT ", "DISTRIBUTED",
"OPENXML", "USE ", "DOUBLE", "OPTION", "USER ",
"DROP", "OR", "VALUES ", "DUMP", "ORDER",
"VARYING ", "ELSE", "OUTER", "VIEW ", "END",
"OVER", "WAITFOR ", "ERRLVL", "PERCENT", "WHEN ",
"ESCAPE", "PIVOT", "WHERE ", "EXCEPT", "PLAN",
"WHILE ", "EXEC", "PRECISION", "WITH ", "EXECUTE",
"PRIMARY", "WITHIN GROUP ", "EXISTS", "PRINT", "WRITETEXT",
"EXIT", "PROC" };
var odbcReservedWords = new List<string> { "ABSOLUTE", "EXEC", "OVERLAPS ", "ACTION",
"EXECUTE", "PAD ", "ADA", "EXISTS", "PARTIAL ",
"ADD", "EXTERNAL", "PASCAL ", "ALL", "EXTRACT",
"POSITION ", "ALLOCATE", "FALSE", "PRECISION ",
"ALTER", "FETCH", "PREPARE ", "AND", "FIRST",
"PRESERVE ", "ANY", "FLOAT", "PRIMARY ", "ARE",
"FOR", "PRIOR ", "AS", "FOREIGN", "PRIVILEGES ",
"ASC", "FORTRAN", "PROCEDURE ", "ASSERTION", "FOUND",
"PUBLIC ", "AT", "FROM", "READ ", "AUTHORIZATION",
"FULL", "REAL ", "AVG", "GET", "REFERENCES ",
"BEGIN", "GLOBAL", "RELATIVE ", "BETWEEN", "GO",
"RESTRICT ", "BIT", "GOTO", "REVOKE ", "BIT_LENGTH",
"GRANT", "RIGHT ", "BOTH", "GROUP", "ROLLBACK ",
"BY", "HAVING", "ROWS ", "CASCADE", "HOUR",
"SCHEMA ", "CASCADED", "IDENTITY", "SCROLL ", "CASE",
"IMMEDIATE", "SECOND ", "CAST", "IN", "SECTION ",
"CATALOG", "INCLUDE", "SELECT ", "CHAR", "INDEX",
"SESSION ", "CHAR_LENGTH", "INDICATOR", "SESSION_USER ",
"CHARACTER", "INITIALLY", "SET ", "CHARACTER_LENGTH",
"INNER", "SIZE ", "CHECK", "INPUT", "SMALLINT ",
"CLOSE", "INSENSITIVE", "SOME ", "COALESCE", "INSERT",
"SPACE ", "COLLATE", "INT", "SQL ", "COLLATION",
"INTEGER", "SQLCA ", "COLUMN", "INTERSECT", "SQLCODE ",
"COMMIT", "INTERVAL", "SQLERROR ", "CONNECT", "INTO",
"SQLSTATE ", "CONNECTION", "IS", "SQLWARNING ", "CONSTRAINT",
"ISOLATION", "SUBSTRING ", "CONSTRAINTS", "JOIN", "SUM ",
"CONTINUE", "KEY", "SYSTEM_USER ", "CONVERT", "LANGUAGE",
"TABLE ", "CORRESPONDING", "LAST", "TEMPORARY ", "COUNT",
"LEADING", "THEN ", "CREATE", "LEFT", "TIME ", "CROSS",
"LEVEL", "TIMESTAMP ", "CURRENT", "LIKE", "TIMEZONE_HOUR ",
"CURRENT_DATE", "LOCAL", "TIMEZONE_MINUTE ", "CURRENT_TIME", "LOWER",
"TO ", "CURRENT_TIMESTAMP", "MATCH", "TRAILING ", "CURRENT_USER",
"MAX", "TRANSACTION ", "CURSOR", "MIN", "TRANSLATE ",
"DATE", "MINUTE", "TRANSLATION ", "DAY", "MODULE",
"TRIM ", "DEALLOCATE", "MONTH", "TRUE ", "DEC",
"NAMES", "UNION ", "DECIMAL", "NATIONAL", "UNIQUE ",
"DECLARE", "NATURAL", "UNKNOWN ", "DEFAULT", "NCHAR",
"UPDATE ", "DEFERRABLE", "NEXT", "UPPER ", "DEFERRED",
"NO", "USAGE ", "DELETE", "NONE", "USER ",
"DESC", "NOT", "USING ", "DESCRIBE", "NULL",
"VALUE ", "DESCRIPTOR", "NULLIF", "VALUES ",
"DIAGNOSTICS", "NUMERIC", "VARCHAR ", "DISCONNECT",
"OCTET_LENGTH", "VARYING ", "DISTINCT", "OF",
"VIEW ", "DOMAIN", "ON", "WHEN ", "DOUBLE",
"ONLY", "WHENEVER ", "DROP", "OPEN", "WHERE ",
"ELSE", "OPTION", "WITH ", "END", "OR",
"WORK ", "END-EXEC", "ORDER", "WRITE ", "ESCAPE",
"OUTER", "YEAR ", "EXCEPT", "OUTPUT", "ZONE" };
var futureReservedWords = new List<string> { "ABSOLUTE", "HOST", "RELATIVE ",
"ACTION", "HOUR", "RELEASE ", "ADMIN", "IGNORE",
"RESULT ", "AFTER", "IMMEDIATE", "RETURNS ",
"AGGREGATE", "INDICATOR", "ROLE ", "ALIAS",
"INITIALIZE", "ROLLUP ", "ALLOCATE", "INITIALLY",
"ROUTINE ", "ARE", "INOUT", "ROW ", "ARRAY",
"INPUT", "ROWS ", "ASENSITIVE", "INT", "SAVEPOINT",
"ASSERTION", "INTEGER", "SCROLL ", "ASYMMETRIC",
"INTERSECTION", "SCOPE ", "AT", "INTERVAL",
"SEARCH ", "ATOMIC", "ISOLATION", "SECOND ",
"BEFORE", "ITERATE", "SECTION ", "BINARY",
"LANGUAGE", "SENSITIVE ", "BIT", "LARGE",
"SEQUENCE ", "BLOB", "LAST", "SESSION ",
"BOOLEAN", "LATERAL", "SETS ", "BOTH", "LEADING",
"SIMILAR ", "BREADTH", "LESS", "SIZE ", "CALL",
"LEVEL", "SMALLINT ", "CALLED", "LIKE_REGEX",
"SPACE ", "CARDINALITY", "LIMIT", "SPECIFIC ",
"CASCADED", "LN", "SPECIFICTYPE ", "CAST",
"LOCAL", "SQL ", "CATALOG", "LOCALTIME",
"SQLEXCEPTION ", "CHAR", "LOCALTIMESTAMP", "SQLSTATE ",
"CHARACTER", "LOCATOR", "SQLWARNING ", "CLASS",
"MAP", "START ", "CLOB", "MATCH", "STATE ",
"COLLATION", "MEMBER", "STATEMENT ", "COLLECT",
"METHOD", "STATIC ", "COMPLETION", "MINUTE",
"STDDEV_POP ", "CONDITION", "MOD", "STDDEV_SAMP ",
"CONNECT", "MODIFIES", "STRUCTURE ", "CONNECTION",
"MODIFY", "SUBMULTISET ", "CONSTRAINTS", "MODULE",
"SUBSTRING_REGEX ", "CONSTRUCTOR", "MONTH", "SYMMETRIC ",
"CORR", "MULTISET", "SYSTEM ", "CORRESPONDING",
"NAMES", "TEMPORARY ", "COVAR_POP", "NATURAL",
"TERMINATE ", "COVAR_SAMP", "NCHAR", "THAN ",
"CUBE", "NCLOB", "TIME ", "CUME_DIST", "NEW",
"TIMESTAMP ", "CURRENT_CATALOG", "NEXT", "TIMEZONE_HOUR ",
"CURRENT_DEFAULT_TRANSFORM_GROUP", "NO", "TIMEZONE_MINUTE ",
"CURRENT_PATH", "NONE", "TRAILING ", "CURRENT_ROLE",
"NORMALIZE", "TRANSLATE_REGEX ", "CURRENT_SCHEMA", "NUMERIC",
"TRANSLATION ", "CURRENT_TRANSFORM_GROUP_FOR_TYPE", "OBJECT",
"TREAT ", "CYCLE", "OCCURRENCES_REGEX", "TRUE ",
"DATA", "OLD", "UESCAPE ", "DATE", "ONLY",
"UNDER ", "DAY", "OPERATION", "UNKNOWN ", "DEC",
"ORDINALITY", "UNNEST ", "DECIMAL", "OUT", "USAGE ",
"DEFERRABLE", "OVERLAY", "USING ", "DEFERRED",
"OUTPUT", "VALUE ", "DEPTH", "PAD", "VAR_POP ",
"DEREF", "PARAMETER", "VAR_SAMP ", "DESCRIBE",
"PARAMETERS", "VARCHAR ", "DESCRIPTOR", "PARTIAL",
"VARIABLE ", "DESTROY", "PARTITION", "WHENEVER ",
"DESTRUCTOR", "PATH", "WIDTH_BUCKET ", "DETERMINISTIC",
"POSTFIX", "WITHOUT ", "DICTIONARY", "PREFIX",
"WINDOW ", "DIAGNOSTICS", "PREORDER", "WITHIN ",
"DISCONNECT", "PREPARE", "WORK ", "DOMAIN",
"PERCENT_RANK", "WRITE ", "DYNAMIC", "PERCENTILE_CONT",
"XMLAGG ", "EACH", "PERCENTILE_DISC", "XMLATTRIBUTES ",
"ELEMENT", "POSITION_REGEX", "XMLBINARY ", "END-EXEC",
"PRESERVE", "XMLCAST ", "EQUALS", "PRIOR",
"XMLCOMMENT ", "EVERY", "PRIVILEGES", "XMLCONCAT ",
"EXCEPTION", "RANGE", "XMLDOCUMENT ", "FALSE", "READS",
"XMLELEMENT ", "FILTER", "REAL", "XMLEXISTS ", "FIRST",
"RECURSIVE", "XMLFOREST ", "FLOAT", "REF", "XMLITERATE ",
"FOUND", "REFERENCING", "XMLNAMESPACES ", "FREE", "REGR_AVGX",
"XMLPARSE ", "FULLTEXTTABLE", "REGR_AVGY", "XMLPI ", "FUSION",
"REGR_COUNT", "XMLQUERY ", "GENERAL", "REGR_INTERCEPT",
"XMLSERIALIZE ", "GET", "REGR_R2", "XMLTABLE ",
"GLOBAL", "REGR_SLOPE", "XMLTEXT ", "GO", "REGR_SXX",
"XMLVALIDATE ", "GROUPING", "REGR_SXY", "YEAR ", "HOLD",
"REGR_SYY", "ZONE" };
var absoluteList = new List<string>();
absoluteList.AddRange(sqlServerReservedWords);
absoluteList.AddRange(odbcReservedWords);
absoluteList.AddRange(futureReservedWords);
absoluteList.AddRange(new[] { "enabled", "name"});
return absoluteList.OrderBy(c => c).Distinct().ToList();
}
private List<ReservedWord> OutputReservedWords(List<string> reserved, List<string> candidate)
{
return (from r in reserved
from w in candidate where r.ToLower() == w.ToLower() select w)
.GroupBy(c=> c)
.Select(c=> new ReservedWord
{Word = c.Key, Count = c.Count()})
.ToList();
}
public class ReservedWord
{
public string Word { get; set; }
public int Count { get; set; }
}
Usage
I have created a CREATE TABLE Statement in Sql Server to demonstrate the scripts output. I have then copied the code to the clipboard and ran the above script in LINQPad. To see the output scroll down.
USE [KanbanTasks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Column](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](max) NULL,
[BoardId] [int] NULL,
CONSTRAINT [PK_Column] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Column] WITH CHECK ADD CONSTRAINT [FK_Column_Boards_BoardId] FOREIGN KEY([BoardId])
REFERENCES [dbo].[Boards] ([Id])
GO
ALTER TABLE [dbo].[Column] CHECK CONSTRAINT [FK_Column_Boards_BoardId]
GO
Output
The script will output a table of reserved words and count. of each word that is used in the script. You can then relook at the script checking that there are no reserved words used in fields.
| Word | Count |
|---|---|
| ADD | 1 |
| CHECK | 2 |
| CLUSTERED | 1 |
| CONSTRAINT | 3 |
| FOREIGN | 1 |
| KEY | 1 |
| NOT | 1 |
| ON | 2 |
| PRIMARY | 1 |