Sunday, August 11, 2013

QUOTENAME()

QUOTENAME()

  
QUOTENAME() returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.   

Syntax
QUOTENAME ( 'character_string' [ , 'quote_character' ] )

Parameters

character_string

Is a string of Unicode character data. character_string is sysname and is limited to 128 characters. Inputs greater than 128 characters return NULL.


quote_character

Is a one-character string to use as the delimiter. Can be a single quotation mark ( ' ), a left or right bracket ( [] ), or a double quotation mark ( " ). If quote_character is not specified, brackets are used.

Why we use QUOTENAME()
SQL Injection is the major drawback in dynamic SQL. A best practice is that we should wrap the objects name with QUOTENAME().
The QUOTENAME() works perfectly for objects names that are less than or equals to 128 characters in length others returns NULL. The QUOTENAME() automatically add brackets.

This example demonstrate the length factors of the QUOTENAME() function
n  Within 128 Character

SELECT QUOTENAME(REPLICATE('A', 10)) As Result

Result
[AAAAAAAAAA]

n  Greeter then 128 characterSELECT QUOTENAME(REPLICATE('A', 129)) As ResultResult
NULL

How QUOTENAME () is used

DECLARE @DynamicSQL NVARCHAR(MAX) = '',
        @Schema     SYSNAME,
        @Table      SYSNAME
       
       
SELECT @DynamicSQL = @DynamicSQL + 'SELECT '''+QUOTENAME(TABLE_SCHEMA)+'.'+ QUOTENAME(TABLE_NAME)+''''+'= COUNT(*)
                   FROM '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME) +';'
                   FROM INFORMATION_SCHEMA.TABLES WHERETABLE_TYPE='BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME

PRINT @DynamicSQL

EXEC sp_executesql @DynamicSQL 


Hope you like it.