Cool little stored procedure that you can install anywhere but I have installed on the Temp database and it tells you who is connected to a database.
It uses sp_who but puts the results into a database that you can query but really all we ever want to know is who is accessing a particular database and sp_who has always been restrictive in that sense.
Easy to call:
EXEC Temp.dbo.ListConnections @sDatabaseName = 'MyDatabaseName'
Hope that helps, Jon Bosker, DB Gurus, 15 Dec 2015
CREATE PROCEDURE dbo.ListConnections
(
@sDatabaseName varchar(MAX)
)
AS
-- Create a table to store the results:
DECLARE @Who TABLE (
[spid] int
, [ecid] int
, [status] varchar(50)
, [loginame] varchar(255)
, [hostname] varchar(255)
, [blk] varchar(50)
, [dbname] varchar(255)
, [cmd] varchar(255)
, [request_id] int
)
-- Put the results of sp_who in there:
INSERT INTO @Who EXEC sp_who
-- Query that table to get the connections to the specified database
SELECT * FROM @Who WHERE dbname = @sDatabaseName
GO
No comments:
Post a Comment