Here's a kill user script I found on the web a long time ago, I've flattened it out to show what it looks like before SQL Prompt format
DECLARE @strSQL VARCHAR(255)
--PRINT 'Killing Users'
--PRINT '-----------------'
CREATE TABLE #tmpUsers (
spid INT,
eid INT,
STATUS VARCHAR(30),
loginname VARCHAR(50),
hostname VARCHAR(50),
blk INT,
dbname VARCHAR(50),
cmd VARCHAR(30),
request_id INT
)
INSERT INTO #tmpUsers
EXEC SP_WHO
DECLARE LoginCursor CURSOR READ_ONLY
FOR
SELECT spid,
dbname
FROM #tmpUsers
WHERE dbname = @dbname
DECLARE @spid VARCHAR(10)
DECLARE @dbname2 VARCHAR(40)
OPEN LoginCursor
FETCH NEXT
FROM LoginCursor
INTO @spid,
@dbname2
WHILE (@@fetch_status <> - 1)
BEGIN
IF (@@fetch_status <> - 2)
BEGIN
--PRINT 'Killing ' + @spid
SET @strSQL = 'KILL ' + @spid
EXEC (@strSQL)
END
FETCH NEXT
FROM LoginCursor
INTO @spid,
@dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP TABLE #tmpUsers
--PRINT 'Done'
Here's the same script after formatting it with SQL Prompt:
DECLARE @strSQL VARCHAR(255)
--PRINT 'Killing Users'
--PRINT '-----------------'
CREATE TABLE #tmpUsers
(
spid INT ,
eid INT ,
STATUS VARCHAR(30) ,
loginname VARCHAR(50) ,
hostname VARCHAR(50) ,
blk INT ,
dbname VARCHAR(50) ,
cmd VARCHAR(30) ,
request_id INT
)
INSERT INTO #tmpUsers
EXEC SP_WHO
DECLARE LoginCursor CURSOR READ_ONLY
FOR
SELECT spid ,
dbname
FROM #tmpUsers
WHERE dbname = @dbname
DECLARE @spid VARCHAR(10)
DECLARE @dbname2 VARCHAR(40)
OPEN LoginCursor
FETCH NEXT
FROM LoginCursor
INTO @spid, @dbname2
WHILE ( @@fetch_status <> -1 )
BEGIN
IF ( @@fetch_status <> -2 )
BEGIN
--PRINT 'Killing ' + @spid
SET @strSQL = 'KILL ' + @spid
EXEC (@strSQL)
END
FETCH NEXT
FROM LoginCursor
INTO @spid, @dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP TABLE #tmpUsers
--PRINT 'Done'
The "Fetch next" are the parts I find odd. Fetch Next gets indented away from FROM LoginCursor INTO ....
then the second fetch next before the END code gets formatted even further out, I guess because it's part of the indent logic for the WHILE BEGIN/END BLOCK?
Here's the same script after formatting it with SQL Prompt:
The "Fetch next" are the parts I find odd. Fetch Next gets indented away from FROM LoginCursor INTO ....
then the second fetch next before the END code gets formatted even further out, I guess because it's part of the indent logic for the WHILE BEGIN/END BLOCK?