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.
