Dedicated SQL Pool: Dynamically Remove Spaces¶

Table of contents (click to expand)

Set Up a Synapse Workspace
Create a Dedicated SQL Pool
Create Tables with Spaces in Names and Columns
Create Views with Modified Tables/Column Names

Set Up a Synapse Workspace¶

Sign in to the Azure Portal: Go to the Azure Portal and sign in with your Azure account.
Navigate to Your Synapse Workspace: In the Azure Portal, search for your Synapse workspace or create a new one if you don't have one.

Create a Dedicated SQL Pool¶

Launch Synapse Studio: From the Synapse workspace overview, click on the Open Synapse Studio button.
Create a Dedicated SQL Pool:
In Synapse Studio, go to the Manage hub by clicking on the Manage icon in the left navigation pane.
Under Analytics pools, select SQL pools and click on the + New button.
Enter the following details:
- SQL pool name: Enter a name for your SQL pool (e.g., SQLPOOL1).
- Performance level: Choose a performance level (e.g., DW1000c).
Click Review + create and then Create to provision the dedicated SQL pool.

Create Tables with Spaces in Names and Columns¶

Open the SQL Script Editor:
In Synapse Studio, go to the Develop hub by clicking on the Develop icon in the left navigation pane.
Click on + New SQL script to open the SQL script editor.

Create Sample Tables: Use the following script to create tables with spaces in their names and columns. Open the complete create-sample-tables.sql file in GitHub.

-- Create sample tables with spaces in names
CREATE TABLE [Employee Records] (
    [Employee ID] INT,
    [Employee Name] NVARCHAR(255),
    [Employee Address] NVARCHAR(255)
);

CREATE TABLE [Sales Data] (
    [Sale ID] INT,
    [Sale Date] DATE,
    [Employee ID] INT,
    [Sale Amount] DECIMAL(10, 2)
);

CREATE TABLE [Inventory Details] (
    [Item ID] INT,
    [Item Name] NVARCHAR(255),
    [Item Category] NVARCHAR(255),
    [Item Price] DECIMAL(10, 2)
);

-- Insert sample data into the tables
INSERT INTO [Employee Records] ([Employee ID], [Employee Name], [Employee Address])
VALUES (1, 'Alice Johnson', '789 Pine St');

INSERT INTO [Employee Records] ([Employee ID], [Employee Name], [Employee Address])
VALUES (2, 'Bob Brown', '101 Maple St');

INSERT INTO [Sales Data] ([Sale ID], [Sale Date], [Employee ID], [Sale Amount])
VALUES (1, '2023-02-01', 1, 200.00);

INSERT INTO [Sales Data] ([Sale ID], [Sale Date], [Employee ID], [Sale Amount])
VALUES (2, '2023-02-02', 2, 250.00);

INSERT INTO [Inventory Details] ([Item ID], [Item Name], [Item Category], [Item Price])
VALUES (1, 'Gadget', 'Electronics', 49.99);

INSERT INTO [Inventory Details] ([Item ID], [Item Name], [Item Category], [Item Price])
VALUES (2, 'Tool', 'Hardware', 29.99);

Run the Script: Execute the script in the SQL script editor to create the tables and insert sample data.

Note

Refresh the workspace explorer after running the script to display the tables.

Create Views with Modified Tables/Column Names¶

Create a Stored Procedure to Remove Spaces from Column Names: Use the following script to create a stored procedure that removes spaces from column names and creates views. Open the complete store-procedure-clean-up.sql file in GitHub.

 CREATE PROCEDURE RemoveSpacesFromColumnNames
 AS
 BEGIN
     DECLARE @tableName NVARCHAR(255)
     DECLARE @columnName NVARCHAR(255)
     DECLARE @sql NVARCHAR(MAX)

     -- Temporary table to store table names
     CREATE TABLE #TableNames (TABLE_NAME NVARCHAR(255))
     INSERT INTO #TableNames
     SELECT TABLE_NAME
     FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo'

     -- Loop through each table
     WHILE EXISTS (SELECT 1 FROM #TableNames)
     BEGIN
         SELECT TOP 1 @tableName = TABLE_NAME FROM #TableNames

         -- Print the table name for debugging
         PRINT 'Processing table: ' + @tableName

         SET @sql = 'CREATE VIEW dbo.vw' + REPLACE(@tableName, ' ', '') + ' AS SELECT '

         -- Drop the temporary table if it exists
         IF OBJECT_ID('tempdb..#ColumnNames') IS NOT NULL
             DROP TABLE #ColumnNames

         -- Temporary table to store column names
         CREATE TABLE #ColumnNames (COLUMN_NAME NVARCHAR(255))
         INSERT INTO #ColumnNames
         SELECT COLUMN_NAME
         FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_NAME = @tableName

         -- Loop through each column
         WHILE EXISTS (SELECT 1 FROM #ColumnNames)
         BEGIN
             SELECT TOP 1 @columnName = COLUMN_NAME FROM #ColumnNames

             -- Print the column name for debugging
             PRINT 'Processing column: ' + @columnName

             -- Remove all spaces from column names
             IF (SELECT COUNT(*) FROM #ColumnNames) = 1
             BEGIN
                 SET @sql = @sql + 'REPLACE([' + @columnName + '], '' '', '''') AS [' + REPLACE(@columnName, ' ', '') + '] '
             END
             ELSE
             BEGIN
                 SET @sql = @sql + 'REPLACE([' + @columnName + '], '' '', '''') AS [' + REPLACE(@columnName, ' ', '') + '], '
             END

             DELETE FROM #ColumnNames WHERE COLUMN_NAME = @columnName
         END

         -- Remove the trailing comma and space if any
         IF RIGHT(@sql, 2) = ', '
         BEGIN
             SET @sql = LEFT(@sql, LEN(@sql) - 2)
         END

         SET @sql = @sql + ' FROM [' + @tableName + '];'

         -- Print the dynamic SQL for debugging
         PRINT 'Generated SQL: ' + @sql

         -- Execute the dynamic SQL
         BEGIN TRY
             EXEC sp_executesql @sql
         END TRY
         BEGIN CATCH
             PRINT 'Error: ' + ERROR_MESSAGE()
         END CATCH

         DELETE FROM #TableNames WHERE TABLE_NAME = @tableName
     END

     -- Clean up temporary tables
     DROP TABLE #TableNames
     DROP TABLE #ColumnNames
 END

Execute the Stored Procedure: Click on Run, to create the stored procedure.

Before After
Run the stored procedure to create views with modified column names.
```
EXEC RemoveSpacesFromColumnNames
```

Before	After

Note

Refresh the workspace explorer after running the procedure to display the views.

Back to the Azure Synapse overview

Review the serverless SQL pool walkthrough