Problem:
Using T-SQL loops for serverless SQL pools in Azure Synapse Analytics
Cursors are not supported in Azure Synapse, either dedicated or the serverless pool. 😈
Writing loops over data is then left to creating a temporary table and iterating over the rows in the dedicated SQL Pool.
For dedicated pools, the solution is listed here Using T-SQL loops - Azure Synapse Analytics | Microsoft Docs
However, for the Serverless pools, creating a temporary table is not supported.
So I tried a work-around, starting from the solution provided by Microsoft in the article above, and got it working for serverless.
Instead of temporary tables, I created a view. Here in this example, I wanted to drop all external tables - as a part of a deploy script (since the dacpac approach doesnt work for serverless, more on that later).
The script
Creates a view from all available external tables,
Gets the counter for a loop over the table records
For each iteration, query that view to get table names & drop them.
The downside with this approach, is with each iteration, the sys.external_tables gets queried.
I would've rather captured the data in a list variable, and iterate over that data. I will try that next.
IF EXISTS (SELECT * FROM sys.views where name = 'Tbl_Tables')
DROP VIEW Tbl_Tables
GO
CREATE VIEW Tbl_Tables
AS SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS MySequence,
s.name as tablename,
s.object_id as table_object_id,
sch.name as schema_nm
from sys.external_tables s inner join
sys.schemas sch on s.schema_id = sch.schema_id;
GO
DECLARE @object_count INT = (SELECT COUNT(*) FROM Tbl_Tables), @i INT = 1;
DECLARE @dynamicstmtdropview VARCHAR(2000);
WHILE @i <= @object_count
BEGIN
DECLARE @table_name VARCHAR(2000) = (
SELECT tablename FROM Tbl_Tables WHERE MySequence = @i)
DECLARE @schema_name VARCHAR(100) = (
SELECT schema_nm FROM Tbl_Tables WHERE MySequence = @i)
SET @dynamicstmtdropview = 'DROP EXTERNAL TABLE '+@schema_name+'.'+ @table_name
print @dynamicstmtdropview
exec (@dynamicstmtdropview)
SET @i += 1
END
