Name	Name	Last commit message	Last commit date
parent directory ..
src	src
README.md	README.md

Dedicated SQL Pool:
Store Procedure Dynamically Remove Space - Overview

Costa Rica

Cloud2BR OSS - Learning Hub

Last updated: 2025-07-16

Table of Content (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. Click here to see the .sql file.

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

Once you refresh, the tables will be visible:

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. Click here to see the .sql file.

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