If you have been working on SQL Server, you would have definitely used sp_who2. One of the limitations of sp_who2 is that you cannot filter the result set directly and we all know how frustrating that can be sometimes. Obviously there are DMVs you can use to get similar information, but if you are like me and like to use the output of sp_who2 to join with other tables and so on , here a way to filter and query the sp_who2. Basically you create a temporary table and insert the output of sp_who2 to the table and Viola ! Here is how :<\/p>\n
DECLARE @Table TABLE(
\nSPID INT,
\nStatus VARCHAR(MAX),
\nLOGIN VARCHAR(MAX),
\nHostName VARCHAR(MAX),
\nBlkBy VARCHAR(MAX),
\nDBName VARCHAR(MAX),
\nCommand VARCHAR(MAX),
\nCPUTime INT,
\nDiskIO INT,
\nLastBatch VARCHAR(MAX),
\nProgramName VARCHAR(MAX),
\nSPID_1 INT,
\nREQUESTID INT
\n);<\/p>\n
INSERT INTO @Table EXEC sp_who2;<\/p>\n
SELECT *
\nFROM @Table
\nWHERE \u00c2\u00a0login = ‘raju’
\norder by DBName;<\/p>\n
Now you can join the @table with any other system catalog to get more info or trend patterns.<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
If you have been working on SQL Server, you would have definitely used sp_who2. One of the limitations of sp_who2 is that you cannot filter the result set directly and we all know how frustrating that can be sometimes. Obviously there are DMVs you can use to get similar information, but if you are like […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[658],"tags":[865,868,866,867,864,863],"class_list":["post-1418","post","type-post","status-publish","format-standard","hentry","category-sql-server-2","tag-filter-sp_who2","tag-join-sp_who2","tag-query-sp_who2","tag-smart-sp_who2","tag-sp_who2","tag-sql-server-tidbits"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1418","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1418"}],"version-history":[{"count":1,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1418\/revisions"}],"predecessor-version":[{"id":1419,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1418\/revisions\/1419"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1418"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1418"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1418"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}