While working on ETL, I build a list of tables and their corresponding columns that need to be created on the destination server. In this case the square brackets around SchemaName and TableName are mandatory. I started adding the square brackets with the built-in function QUOTENAME(), but soon I got a “dirty” data and created a user-defined scalar function that adds square brackets around SchemaName and TableName.
Create database
USE [master]; GO IF (DB_ID('PL') IS NOT NULL) BEGIN ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE PL; END GO CREATE DATABASE PL; GO USE PL; GO
Create function
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF (OBJECT_ID('dbo.udf_QuoteName', 'FN') IS NOT NULL) BEGIN DROP FUNCTION dbo.udf_QuoteName; END GO /*============================================= Author: Peter Lalovsky Create date: 2017-10-27 Description: Extend the functionality of QUOTENAME() by removing additional square brackets =============================================*/ CREATE FUNCTION dbo.udf_QuoteName (@String NVARCHAR(255)) RETURNS NVARCHAR(255) AS BEGIN DECLARE @ReturnValue NVARCHAR(255) DECLARE @Position INT = 1 , @LenString INT; SELECT @LenString = LEN(@String); -- Left Square Brackets WHILE (@Position <= @LenString) BEGIN IF LEFT(@String, 1) = '[' BEGIN SET @String = RIGHT(@String, LEN(@String) - 1); END ELSE BEGIN BREAK; END SELECT @Position += 1; END -- Right Square Brackets SELECT @Position = 1; WHILE (@Position <= @LenString) BEGIN IF RIGHT(@String, 1) = ']' BEGIN SET @String = LEFT(@String, LEN(@String) - 1); END ELSE BEGIN BREAK; END SELECT @Position += 1; END SELECT @ReturnValue = CONCAT('[', @String, ']'); --RETURN QUOTENAME(REPLACE(REPLACE(@String, '[', ''), ']', '')) RETURN @ReturnValue; END GO
The highlighted line (row 55) shows another approach that bulk replace the square brackets and adds QUOTENAME()
To test the function, we need a table with sample data
IF (OBJECT_ID('tempdb.dbo.#DBObjects', 'U') IS NOT NULL) BEGIN DROP TABLE #DBObjects; END SELECT '[[Schema Name 1]]]' AS SchemaName, 'Table Name 1]' AS TableName INTO #DBObjects UNION ALL SELECT 'Schema Name 2]', '[Table Name 2' UNION ALL SELECT 'Schema Name 3', '[Table Name 3]' UNION ALL SELECT 'Schema Name 3', 'Table Name 4' UNION ALL SELECT 'Schema Name 3', 'Table Name 4' UNION ALL SELECT 'Schema [ Name 3', 'Table] Name 4'; SELECT * FROM #DBObjects;
Let’s check QUOTENAME()
SELECT QUOTENAME(SchemaName) AS SchemaName , QUOTENAME(TableName) AS TableName FROM #DBObjects; GO

Test the function
SELECT dbo.udf_QuoteName(SchemaName) AS SchemaName , dbo.udf_QuoteName(TableName) AS TableName FROM #DBObjects; GO
Keep it simple :-)