DBCC UPDATEUSAGE and sp_updatestats

This is one of the important issue that i needed in my projects. When we want to process backup or restore a database, we should make sure that no users has connected. This small procedure helps to kills the connection of all the users of the specfied Database.

CREATE PROCEDURE sp_UserKill( @DbName varchar(255))AS
BEGIN

DECLARE @strErrMsg nvarchar(255)
IF NOT EXISTS (SELECT TOP 1 Name from master..sysdatabases where name = @DbName)BEGIN
SET @strErrMsg = 'Database '+@Dbname+' does not exists '
RAISERROR(@ErrMsg,16,1) RETURN
END

DECLARE @strKill nvarchar(255)
DECLARE #c_TaskList CURSOR
FOR SELECT 'kill '+convert(varchar(5),spid) +' -- '+p.loginame FROM master..sysprocesses p INNER JOIN master..sysdatabases d ON p.dbid = d.dbid WHERE d.name = @DbName
OPEN #c_TaskList
FETCH NEXT FROM #c_TaskList into @strKill
WHILE @@fetch_status = 0
BEGIN
EXEC(@strKill)
FETCH NEXT FROM #c_TaskList into @strKill
END
CLOSE #c_TaskList
DEALLOCATE #c_TaskList
END