Easily removing repeated lines using Excel

Imagine that you’ve just used SQL Server Management Studio to generate a script, and it’s done it’s job wonderfully, except for a lot of unsightly GO and blank lines:

GRANT SELECT ON [Person].[Address] TO [SomeUser];
GO

GRANT INSERT ON [Person].[Address] TO [SomeUser];
GO

GRANT UPDATE ON [Person].[Address] TO [SomeUser];
GO

GRANT DELETE ON [Person].[Address] TO [SomeUser];
GO

Although technically correct, this may offend your sense of aesthetics. If there’s only a couple of rows (as in the excerpt above, you can manually remove the unsightly GOs and blank lines, but this is a bigger ask when you have more than about 20 commands.

The simplest method I’ve come across is to use one of the DBA’s most trusted tools – Excel. Although there are other ways of doing this sort of operation, Excel is installed on almost every Windows machine, and this is a quick operation.

First, copy the entire set of commands, and paste into Excel as Column B, and in column A, add a number for each row (essentially an IDENTITY column). Remember that you can just fill out 1 and 2, select both of these, and drag down to the bottom:

This number column is used so that we know which order each row was in originally, because we’re about to sort the data. Select columns A and B for all rows, and sort by column B:

Next, select the rows you don’t want, and delete them.

Now, select the remaining data, and sort again, this time on column A, to return to the original sort order (minus the offending rows):

Copy the commands back into Management Studio, and you’re done.

GRANT SELECT ON [Person].[Address] TO [SomeUser];
GRANT INSERT ON [Person].[Address] TO [SomeUser];
GRANT UPDATE ON [Person].[Address] TO [SomeUser];
GRANT DELETE ON [Person].[Address] TO [SomeUser];