e.etl-unit.etlunit-sql-server-database.1.6.6.source-code.kill_db.vm Maven / Gradle / Ivy

Go to download
USE MASTER

DECLARE @LOGINS TABLE
(
	SPID INT NOT NULL,
	STATUS VARCHAR(256),
	LOGIN_NAME VARCHAR(256),
	HOST_NAME VARCHAR(256),
	BLOCKED_BY VARCHAR(256),
	DATABASE_NAME VARCHAR(256),
	COMMAND VARCHAR(256),
	CPU_TIME VARCHAR(256),
	DISKIO VARCHAR(256),
	LAST_BATCH VARCHAR(256),
	PROGRAM_NAME VARCHAR(256),
	SPID_2 VARCHAR(256),
	REQUEST_ID VARCHAR(256)
)

INSERT @LOGINS EXEC SP_WHO2

DECLARE login_cursor CURSOR FOR
SELECT SPID FROM @LOGINS WHERE LOGIN_NAME = '${databaseLogin}'

DECLARE @SPID INT
DECLARE @SQL VARCHAR(64)

OPEN login_cursor;
	FETCH NEXT FROM login_cursor INTO @SPID;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @SQL = 'KILL ' + CONVERT(VARCHAR(5), @SPID);

		EXEC(@SQL)

		FETCH NEXT FROM login_cursor INTO @SPID;
	END

CLOSE login_cursor;
DEALLOCATE login_cursor;

IF DB_ID('${databaseName}') IS NOT NULL
BEGIN
	ALTER DATABASE ${databaseName}
	SET SINGLE_USER WITH ROLLBACK IMMEDIATE

	DROP DATABASE ${databaseName}
END

;;

	IF EXISTS (SELECT NAME FROM MASTER.DBO.SYSLOGINS WHERE NAME = '${databaseLogin}')
	BEGIN
	DROP LOGIN ${databaseLogin}
	END
;;