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 character
SELECT QUOTENAME(REPLICATE('A', 129)) As Result
Result

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 WHERE TABLE_TYPE='BASE TABLE'

ORDER BY TABLE_SCHEMA, TABLE_NAME

PRINT @DynamicSQL

EXEC sp_executesql @DynamicSQL 

Hope you like it.

Posted by: MR. JOYDEEP DAS