SQL Function QUOTENAME()

Hello all,

I support third party software databases at work. During the implementation of the application it needed to create a database to store the data. However the application created a database with a name that does not follow the rules for the format of identifiers. In my case we ended up with a database name '20120101_Survey'.

On the same server I have a custom built script that backups up all the databases at night. However, following the database creation my backup process failed and I got an alert about the process. Further looking into the problem I found out that there was a problem with the syntax. As soon as I see the statement I knew what the problem was but never thought about it while deploying the application and database. To keep the story short I used the SQL Server function called QUOTENAME() to fix the problem. Using the function in my custom script help me return the database names with delimiters so that backup process completes without error. Since then I have been using the function with all the object names. This simple function can prevent some headaches if a DBA needs to support 3rd party databases (SharePoint is also a good example because the SharePoint database names don't follow the rules of the identifier format). Here is the link to the msdn and below is simple statement that returns all the databases in your system with delimiters.

SELECT QUOTENAME(name) AS DelimitedDbName

FROM sys.databases

HTH,

Bulent