Parallel Data Warehouse (PDW): To close all the session connected to the database and then drop the database in PDW (Microsoft APS).

close_pdw_sessions_to_restore_the_database.sql

-- 1. Get all the session id for the database.
-- 2. Kill all the session id.
-- 3. Drop database
--================================================
 
DECLARE @TOTAL_CONNECTIONS INT;
DECLARE @INDEX INT;
DECLARE @RESTORE_DB_NAME VARCHAR(100);
DECLARE @SQL_KILL_SESSION NVARCHAR(1000);
DECLARE @SQL_DROP_DB NVARCHAR(1000);
 
 
SET @RESTORE_DB_NAME = 'DB_TO_BE_DROPPED' --> database name
SET @SQL_DROP_DB = 'DROP DATABASE '+@RESTORE_DB_NAME
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.DatabaseConnection'))
       DROP TABLE dbo.DatabaseConnection
 
CREATE TABLE dbo.DatabaseConnection WITH (DISTRIBUTION = REPLICATE ) AS
       SELECT
       SESSION_ID,
       ROW_NUMBER = ROW_NUMBER() OVER(ORDER BY SESSION_ID)  
       FROM SYS.DM_PDW_LOCK_WAITS
       WHERE OBJECT_TYPE='DATABASE' AND OBJECT_NAME=@RESTORE_DB_NAME
 
SET @INDEX = 1;
SET @TOTAL_CONNECTIONS = (SELECT COUNT(*) FROM dbo.DatabaseConnection);
 
WHILE @INDEX <= @TOTAL_CONNECTIONS
       BEGIN
              SET @SQL_KILL_SESSION = (SELECT 'KILL ''' + CAST(SESSION_ID AS NVARCHAR(10)) + '''' FROM dbo.DatabaseConnection WHERE ROW_NUMBER=@INDEX)
              PRINT @SQL_KILL_SESSION
              EXEC SP_EXECUTESQL @SQL_KILL_SESSION
              SET @INDEX += 1
       END
 
IF  EXISTS (SELECT * FROM sys.databases WHERE name = @RESTORE_DB_NAME )
       --EXEC SP_EXECUTESQL @SQL_DROP_DB --***DROP DATABASE***
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.DatabaseConnection'))
       DROP TABLE dbo.DatabaseConnection