Sp_who - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sp_who

Author

Topic

stumbling
Posting Yak Master

104 Posts

Posted - 2008-10-19 : 23:07:10

Hi All

I know of using sp_who and sp_who2 to see active connections on all DataBases but is there any other commands that just limit it to one database?

Cheers
Phil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 01:31:41

have a look at sys.dm_exec_sessions dynamic management view.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 01:49:26

Also you could put the results of sp_who or sp_who2 to a temporary table created and filter on DBName column to get the results confined to a db. Please note that the structure of temporary table created should be same as resultset returned by sp_who/sp_who2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 01:56:10

something like

declare @test table
(
spid int,
status varchar(100),
loginname varchar(2000),
hostname varchar(2000),
blkby varchar(100),
dbname varchar(200),
cmd varchar(max),
cputime int,
diskio int,
lastbatch varchar(100),
pgmname varchar(500),
parentspid int,
request_id int
)

insert into @test
EXEC sp_who2


select * from @test where dbname ='Yourdbname'

please note that you need to use #tables if using sql 2000 or earlier as insert exec wont work with @tables in 2000 and earlier versions.

stumbling
Posting Yak Master

104 Posts

Posted - 2008-10-20 : 02:44:44

Thanks for that i am using 2000 and ihave written it like this but why is it that the #temp DB does not drop if i run this more than once? Any Ideas?
Cheers
Phil

if exists (select * from dbo.sysobjects where id = object_id(N'[#temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [#temp]
GO

create table #temp
(
spid int,
status varchar(100),
loginname varchar(2000),
hostname varchar(2000),
blkby varchar(100),
dbname varchar(200),
cmd varchar(2000),
cputime int,
diskio int,
lastbatch varchar(100),
pgmname varchar(500),
parentspid int,
)
insert into #temp
EXEC sp_who2
select * from #temp where dbname ='Insert DB NAME'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 02:47:44

quote:
Originally posted by stumbling

Thanks for that i am using 2000 and ihave written it like this but why is it that the #temp DB does not drop if i run this more than once? Any Ideas?
Cheers
Phil

if exists (select * from dbo.sysobjects where id = object_id(N'[#temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [#temp]
GO

create table #temp
(
spid int,
status varchar(100),
loginname varchar(2000),
hostname varchar(2000),
blkby varchar(100),
dbname varchar(200),
cmd varchar(2000),
cputime int,
diskio int,
lastbatch varchar(100),
pgmname varchar(500),
parentspid int,
)
insert into #temp
EXEC sp_who2
select * from #temp where dbname ='Insert DB NAME'
drop table #temp

because the scope of #temp table is connection so that it will remain until you close the connection. so if you want to run it subsequently in same connection make sure you've a statement to drop table after each run explicitly (just like in blue code above

stumbling
Posting Yak Master

104 Posts

Posted - 2008-10-20 : 03:07:15

Its ok i know why doh my thoughts exactly you hjust beat me to the post. Cheers and thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 04:10:07

quote:
Originally posted by stumbling

Its ok i know why doh my thoughts exactly you hjust beat me to the post. Cheers and thanks a lot.

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources