Removing [square brackets] from generated SSMS scripts

The number of people who really love having their code [quoted] with [square brackets] is pretty small. The vast majority of people who I meet really hate them, and promptly remove them.

If everyone hates them, why are they there?

If you’re generating CREATE/ALTER/DROP/etc scripts for objects, whether using SQL Server Management Studio, SQL Ops Studio, or PowerShell, those scripts are generated by SMO–the SQL Server Management Objects. SMO wants to [quote] everything.

There are cases where you need brackets, such as having objects with “illegal” characters in them. These rules apply to database names, column names, and all object names. (I’m going to simply refer to “object names” for simplicity, though I concede that “identifier” might be a more correct term.) If you want to put a space or hyphen in an object name, then you’re going to have to use [brackets] to “quote” the name every time you reference it. Similarly, you can’t start an object name with a number, even though it’s a valid character in any other position of the object name: 8Ball is an illegal object name, but AM2 is perfectly OK. There are a bunch of other scenarios, but I won’t go into them all.

If SMO tried to only use [brackets] where necessary, that would likely be a complicated and error-prone branch of code. It’s safer to always include [brackets], and there’s no time when [brackets] will break your code.

Except [brackets] break readability

I think having all those [brackets] everywhere makes code much less readable. This is a scenario where I bend the rules on my “same way, every day” philosophy of coding. Normally, if there’s something that I only need to do sometimes, and doing it always isn’t harmful, then I just do it all the time. Putting semicolons at the end of every statement is a perfect example of this: I have to have them sometimes, they never hurt, so I should just do it every time.

In the case of [brackets], I think they hurt readability enough that it’s worth the inconsistency. Also, if I name all my objects properly, then I can skip [quoting] in almost all cases! One big exception being in dynamic SQL: I make liberal use of QUOTENAME() in dynamic SQL, so the generated code will have [brackets].

Quit your story telling, Andy. Help me get rid of them!

Ok, ok. Sorry, I like to tell stories.

If you script out a table in SSMS, and it produces a script that looks like this (example from Wide World Importers sample database):

CREATE TABLE [Application].[Cities](
	[CityID] [int] NOT NULL,
	[CityName] [nvarchar](50) NOT NULL,
	[StateProvinceID] [int] NOT NULL,
	[Location] [geography] NULL,
	[LatestRecordedPopulation] [bigint] NULL,
	[LastEditedBy] [int] NOT NULL,
	[ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
	[ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT [PK_Application_Cities] PRIMARY KEY CLUSTERED 
(
	[CityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [USERDATA],
	PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
) ON [USERDATA] TEXTIMAGE_ON [USERDATA]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [Application].[Cities_Archive] )
)

All those [brackets] offend my sensibilities, and make the code hard to read. I want them gone! I hit CTRL+H to bring up the Quick Replace window:

Sure, I could search for [ and then search for ] and replace both with nothing… but I’ve had a few cases when I’m scripting out and formatting a large number of objects into separate files and it gets tiring–I really want to do just one find/replace and be done.

It’s going to be way easier to use a regular expression. In the Find box, type “[\[\]]” and hit the RegEx button (it looks like “.*”). Leave the Replace text box blank, and hit the Replace All button:

This will strip out all the [brackets] and your code will look like this:

CREATE TABLE Application.Cities(
	CityID int NOT NULL,
	CityName nvarchar(50) NOT NULL,
	StateProvinceID int NOT NULL,
	Location geography NULL,
	LatestRecordedPopulation bigint NULL,
	LastEditedBy int NOT NULL,
	ValidFrom datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
	ValidTo datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT PK_Application_Cities PRIMARY KEY CLUSTERED 
(
	CityID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON USERDATA,
	PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) ON USERDATA TEXTIMAGE_ON USERDATA
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = Application.Cities_Archive )
)

In SQL Ops Studio, the Quick Replace window is nearly identical, and it works just the same. Most other IDEs and text editors have similar functionality, too.

Queue the sad panda…This sample database has a schema named Application. This is a reserved keyword, so I’ll need to add [brackets] back in to quote that schema name. There’s also a column named Location, and that should be quoted too. You can tell I didn’t create this table because I wouldn’t have used reserved keywords in those two places.

How it works

I threw that RegEx at you without any explanation, and it works… but why? With RegEx, you can do a “Search for any one of these characters” searches by listing characters within square brackets. So if I want to find “a” or “e” or “i” or “o” or “u”, I would do a RegEx find for [aeiou]. I can also search for ranges, where if I want to find “a” or “b” or “c” or “d”, I can do a RegEx find for [a-d]. Pretty cool, huh?

When I want to search for [brackets], I need to take an extra step. I can’t search for [[]] because the [brackets] are supposed to delimit the search, and the extra brackets gets confusing. I need to escape them, and in RegEx you use a \backslash preceding the character to escape it. So I want to search for either \[ or \[ in order to find (and replace) the [brackets].