Monday, 14 December 2015

List the connections to a database (sp_who saved into a table)

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