THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

Replacing Quoted Identifiers with Bracketed Identifiers

So I just generated a DDL script using Visio and it uses quoted identifiers for all object names. But I don't like quoted identifiers and much more prefer bracketed identifiers. So how do I manage to replace all those pesky double quotes? Why, simply use "Find and Replace" in SQL Server Management Studio (SSMS)!

  1. SAVE THE SCRIPT FILE (just in case) before doing this operation!
  2. Open the Find and Replace dialog.
  3. At the bottom (in the "Find options") is a check box called "Use:". Check it.
  4. Select "Regular expressions" from the drop list below it.
  5. Then enter the following:
    1. Find what:
      \"{<.@>}\"
    2. Replace with:
      \[\1\]
  6. Click Replace All.

That's it, you now have bracketed identifiers for your objects.

WARNING: This changes all quoted text, including data in INSERT, UPDATE, or even DELETE statements, and therefore could cause issues with your data. This is meant to be used only with DDL scripts that contain no data. And please check before saving again and better yet, check the code into source control before doing the massive replace.

Published Saturday, September 09, 2006 8:32 PM by Peter W. DeBetta

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Joe Celko said:

>> I don't like quoted identifiers and much more prefer bracketed identifiers <<

Why would you actually want to go from ANSI Standards to proprietary syntax? Do you have other scripts to needlessly destroy portability, readability and make maintaining code harder?  

It sounds like the Monty Python routine about nailing bottle caps to your floor so that you can get the feeling of walking on metal bottles in your bare feet ...
September 17, 2006 10:38 AM
 

take it ease said:

I think Joe needs to take some stool softener.  He sounds pretty upset and constipated today.
September 17, 2006 8:24 PM
 

"Fan" of Joe said:

It's not just today.

And Joe is only the runner-up for being the most cantankerous, contemptible, wretched guy in the database world.
September 18, 2006 9:02 AM
 

dBoy said:

Actually, I agree with Joe -- except for the Monty Python reference -- it was actually Flanders and Swann (At the Drop of a Hat)
September 18, 2006 6:15 PM
 

"Fan" of Joe said:

Sure, Joe is right some of the time (though not very often), but he's an @$$ all of the time.
September 19, 2006 8:17 AM
 

Scott Whigham said:

Joe -

As far as SQL Server 2005 developers/DBAs using brackets vs quoted-identifiers, it's just plain harder to consistently use quoted identifiers instead of brackets. If you are a "SQL Server only" developer (like me and most likely my fellow Dallasite, Peter, are) you prefer to use brackets. When I develop *my* code for my projects/databases, 100% of the time I'm going to use brackets. If I'm code-managing someone else's code that's only going to use SQL Server, I'll go with the client's convention.

Personally, I would be happy if the standard was modded to include brackets. If you program in multiple languages (C# and SQL are mine), for example, and you want to reference a view that has spaces in the name inside your C#/ADO.NET code, you have to do escaping to reference it with double quotes. Yeeecccchhhh. Is that better? No - it's syntax crap. Brackets should become an optional part of the standard IMO.

What makes brackets easier in Microsoft SQL Server?
(1) Find/replace is a breeze
(2) No need to escape:
SqlCommand myCommand = new SqlCommand("SELECT * FROM "MyView""); // doesn't work
SqlCommand myCommand = new SqlCommand("SELECT * FROM \"MyView\""); // compiles but is crap to write/maintain
SqlCommand myCommand = new SqlCommand("SELECT * FROM [MyView]"); // compiles

It's the same for any programmer of nearly any language. Double quotes are usually an integral part of a programming language and, when you're writing Java/C++/C#/VB/language-of-choice and you need to reference a SQL object that has reserved words/spaces in the name, you either have to escape the double quotes or (if you're *lucky* enough to be using SQL Server) use brackets. I prefer brackets :)
September 30, 2006 5:18 AM
 

Sylvain said:

I agree. And if you want to use a specific shema like [MySchema].[MyTable], scripts generated by Visio, using quote instead of bracket make you create tables in the dbo schema anyway : dbo.MySchema.MyTable !!!

December 10, 2008 11:18 AM

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement