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 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

Related Posts