Dynamic SQL and square brackets in SQL Server identifiers
All the following identifiers are valid column names (the resulting name is shown in the comments):
CREATE TABLE Table1
(
[[MyColumn] int, – [MyColumn
[My[Column] int, – My[Column
[[[MyColumn] int, – [[MyColumn
[MyColumn]]] int, – MyColumn]
[]]MyColumn] int, – ]MyColumn
[[]]MyColumn] int – []MyColumn
)
These are called delimited identifiers are also valid for other kind of objects like tables, constraints and so on. Obviously they should be avoided, but it’s good to be aware that they may exist in case you have to work with legacy databases or dynamic SQL. SQL Server allows any character in the current code page to be used in a delimited identifier, but square brackets in particular can break that beautiful dynamic SQL script you are working on. For example, let’s write a script that drops all the columns in the Table1 table (an artificial example but it serves to illustrate the point):
DECLARE @Command nvarchar(2000), @ColumnName sysname
DECLARE ColumnsCursors CURSOR FOR
SELECT c.name
FROM sys.tables t
INNER JOIN sys.columns c ON c.object_id = t.object_id
WHERE t.name = ‘Table1′
OPEN ColumnsCursors
FETCH NEXT FROM ColumnsCursors INTO @ColumnName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Command = ‘ALTER TABLE Table1 DROP COLUMN ‘ + @ColumnName
PRINT @Command
EXEC sp_executesql @Command
FETCH NEXT FROM ColumnsCursors INTO @ColumnName;
END;
CLOSE ColumnsCursors;
DEALLOCATE ColumnsCursors;
The script will fail with all sorts of errors:
ALTER TABLE Table1 DROP COLUMN [MyColumn
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'MyColumn'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'MyColumn'.
ALTER TABLE Table1 DROP COLUMN My[Column
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'Column'.
ALTER TABLE Table1 DROP COLUMN [[MyColumn
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string '[MyColumn'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '[MyColumn'.
ALTER TABLE Table1 DROP COLUMN MyColumn]
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘]’.
ALTER TABLE Table1 DROP COLUMN ]MyColumn
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘]’.
ALTER TABLE Table1 DROP COLUMN []MyColumn
Msg 1038, Level 15, State 4, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘MyColumn’.
Enclosing all the identifiers with square brackets (using ‘[‘ + @ColumnName + ‘]’) is not enough, the drop of the last 3 columns will still fail:
ALTER TABLE Table1 DROP COLUMN [MyColumn]]
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ‘MyColumn]’.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘MyColumn]’.
ALTER TABLE Table1 DROP COLUMN []MyColumn]
Msg 1038, Level 15, State 4, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘MyColumn’.
ALTER TABLE Table1 DROP COLUMN [[]MyColumn]
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘MyColumn’.
To be completely safe, you also need to escape any occurrence of “]” in the identifier with “]]”:
SET @Command = ‘ALTER TABLE Table1 DROP COLUMN [' + REPLACE(@ColumnName, ']‘, ‘]]’) + ‘]’
Note that the script will still fail on the last step because you can’t drop all the columns of a table, but you get the idea. Hope it helps.