Options

SQL Compare has issues with columns that include [ or ]

NiallNiall Posts: 36 Bronze 1
edited September 25, 2015 4:11AM in SQL Compare 11
In our datawarehouse we use columns with the [ and ] characters in them, such as "Example.[Something].[SomethingElse]" this is to allow for script built solutions and to avoid name collisions. Unfortuntly SQL Compare has a few bugs in it when using [ and ], so far this has impacted the annoying automatic mapping when it tries to rename columns and also where a computed column is used. The tool does not correctly 'quote' the name which for the column mentioned would be [Example.[Something]].[SomethingElse]]] or "Example.[Something].[SomethingElse]", where using [] to quote the name the closing ] in the column name part needs to be ]]. Though a far far simpler solution would be to use the built in SQL Function QUOTENAME() which when provided with no second parameter will use [] to quote. ie

SELECT QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS

will give the correct form.

Note: when using sp_rename the @newname part should not be quoted to rename Example.[Something].[SomethingElse] to Example.[Something].[SomethingDifferent] the format is

EXECUTE sp_rename N'Niall.Test.[Example.[Something]].[SomethingElse]]]', N'Example.[Something].[SomethingDifferent]', 'COLUMN'

At the moment RedGate Compare fails to correctly quote the column name for the first (@objname) parameter
· Share on Twitter

Comments

Sign In or Register to comment.