Retrieve Processes Using Specified Database | SansSQL

Thursday, March 20, 2008

Retrieve Processes Using Specified Database

If you want to know how many processes are there in any particular database, it can be retrieved querying sysprocesses in master database.
1st Method
USE master
GO
DECLARE @dbid INT
SELECT @dbid = dbid
FROM sysdatabases
WHERE name = ‘DBName’
IF EXISTS (SELECT spid
FROM sysprocesses
WHERE dbid = @dbid)
BEGIN
SELECT ‘These processes are using current database’ AS Note,
spid, last_batch,
status, hostname, loginame
FROM sysprocesses
WHERE dbid = @dbid
END
GO

2nd Method
SELECT 'These processes are using database ' AS Note,
[Database] =DB_NAME (dbid), spid, last_batch,
status, hostname, loginame
FROM sysprocesses
WHERE dbid = DB_ID (‘DBName')

Ads