{"id":339,"date":"2014-11-21T10:00:20","date_gmt":"2014-11-21T10:00:20","guid":{"rendered":"http:\/\/www.dbafox.com\/?p=339"},"modified":"2015-07-10T14:20:32","modified_gmt":"2015-07-10T14:20:32","slug":"display-users-table-variable-example-sql-server","status":"publish","type":"post","link":"https:\/\/dbafox.com\/?p=339","title":{"rendered":"Display users using sp_who, sp_who2 &#8211; SQL Server"},"content":{"rendered":"<pre>\r\n-- see users logged - table variable example \r\nDECLARE @whotable TABLE\r\n(\r\n   SPID INT,  \r\n   Status VARCHAR(1000) NULL,  \r\n   Login SYSNAME NULL,  \r\n   HostName SYSNAME NULL,  \r\n   BlkBy SYSNAME NULL,  \r\n   DBName SYSNAME NULL,  \r\n   Command VARCHAR(1000) NULL,  \r\n   CPUTime INT NULL,  \r\n   DiskIO INT NULL,  \r\n   LastBatch VARCHAR(1000) NULL,  \r\n   ProgramName VARCHAR(1000) NULL,  \r\n   SPID2 INT,\r\n   REQUESTID INT\r\n)\r\n\r\nINSERT INTO @whotable EXEC sp_who2\r\n\r\nselect * from @whotable\r\n<\/pre>\n<pre>\r\n-- see users logged - temp table example\r\nCREATE TABLE #sp_who2 \r\n(\r\n   SPID INT,  \r\n   Status VARCHAR(1000) NULL,  \r\n   Login SYSNAME NULL,  \r\n   HostName SYSNAME NULL,  \r\n   BlkBy SYSNAME NULL,  \r\n   DBName SYSNAME NULL,  \r\n   Command VARCHAR(1000) NULL,  \r\n   CPUTime INT NULL,  \r\n   DiskIO INT NULL,  \r\n   LastBatch VARCHAR(1000) NULL,  \r\n   ProgramName VARCHAR(1000) NULL,  \r\n   SPID2 INT,\r\n   REQUESTID INT\r\n) \r\nGO\r\n\r\nINSERT INTO #sp_who2\r\nEXEC sp_who2\r\nGO\r\n\r\nSELECT *\r\nFROM #sp_who2\r\nGO\r\n\r\nDROP TABLE #sp_who2\r\nGO\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>&#8212; see users logged &#8211; table variable example DECLARE @whotable TABLE ( SPID INT, Status VARCHAR(1000) NULL, Login SYSNAME NULL, HostName SYSNAME NULL, BlkBy SYSNAME NULL, DBName SYSNAME NULL, Command VARCHAR(1000) NULL, CPUTime INT NULL, DiskIO INT NULL, LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL, SPID2 INT, REQUESTID INT ) INSERT INTO @whotable EXEC sp_who2 select&hellip;&nbsp;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","pgc_sgb_lightbox_settings":"","footnotes":""},"categories":[19,24,28,29,1],"tags":[],"class_list":["post-339","post","type-post","status-publish","format-standard","hentry","category-linux","category-ms-sql-server","category-oracle-technical","category-powershell","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Display users using sp_who, sp_who2 - SQL Server - dbafox<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbafox.com\/?p=339\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Display users using sp_who, sp_who2 - SQL Server - dbafox\" \/>\n<meta property=\"og:description\" content=\"-- see users logged - table variable example DECLARE @whotable TABLE ( SPID INT, Status VARCHAR(1000) NULL, Login SYSNAME NULL, HostName SYSNAME NULL, BlkBy SYSNAME NULL, DBName SYSNAME NULL, Command VARCHAR(1000) NULL, CPUTime INT NULL, DiskIO INT NULL, LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL, SPID2 INT, REQUESTID INT ) INSERT INTO @whotable EXEC sp_who2 select&hellip;&nbsp;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbafox.com\/?p=339\" \/>\n<meta property=\"og:site_name\" content=\"dbafox\" \/>\n<meta property=\"article:published_time\" content=\"2014-11-21T10:00:20+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2015-07-10T14:20:32+00:00\" \/>\n<meta name=\"author\" content=\"Ray Fox\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ray Fox\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=339#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=339\"},\"author\":{\"name\":\"Ray Fox\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\"},\"headline\":\"Display users using sp_who, sp_who2 &#8211; SQL Server\",\"datePublished\":\"2014-11-21T10:00:20+00:00\",\"dateModified\":\"2015-07-10T14:20:32+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=339\"},\"wordCount\":10,\"publisher\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\"},\"articleSection\":[\"Linux\",\"MS SQL Server\",\"Oracle Technical\",\"PowerShell\",\"Uncategorized\"],\"inLanguage\":\"en-GB\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=339\",\"url\":\"https:\\\/\\\/dbafox.com\\\/?p=339\",\"name\":\"Display users using sp_who, sp_who2 - SQL Server - dbafox\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#website\"},\"datePublished\":\"2014-11-21T10:00:20+00:00\",\"dateModified\":\"2015-07-10T14:20:32+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=339#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/dbafox.com\\\/?p=339\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=339#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/dbafox.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Display users using sp_who, sp_who2 &#8211; SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/#website\",\"url\":\"https:\\\/\\\/dbafox.com\\\/\",\"name\":\"dbafox\",\"description\":\"DBA Technical Information\",\"publisher\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/dbafox.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\",\"name\":\"Ray Fox\",\"logo\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/image\\\/\"},\"sameAs\":[\"http:\\\/\\\/www.dbafox.com\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Display users using sp_who, sp_who2 - SQL Server - dbafox","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbafox.com\/?p=339","og_locale":"en_GB","og_type":"article","og_title":"Display users using sp_who, sp_who2 - SQL Server - dbafox","og_description":"-- see users logged - table variable example DECLARE @whotable TABLE ( SPID INT, Status VARCHAR(1000) NULL, Login SYSNAME NULL, HostName SYSNAME NULL, BlkBy SYSNAME NULL, DBName SYSNAME NULL, Command VARCHAR(1000) NULL, CPUTime INT NULL, DiskIO INT NULL, LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL, SPID2 INT, REQUESTID INT ) INSERT INTO @whotable EXEC sp_who2 select&hellip;&nbsp;","og_url":"https:\/\/dbafox.com\/?p=339","og_site_name":"dbafox","article_published_time":"2014-11-21T10:00:20+00:00","article_modified_time":"2015-07-10T14:20:32+00:00","author":"Ray Fox","twitter_misc":{"Written by":"Ray Fox","Estimated reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbafox.com\/?p=339#article","isPartOf":{"@id":"https:\/\/dbafox.com\/?p=339"},"author":{"name":"Ray Fox","@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"headline":"Display users using sp_who, sp_who2 &#8211; SQL Server","datePublished":"2014-11-21T10:00:20+00:00","dateModified":"2015-07-10T14:20:32+00:00","mainEntityOfPage":{"@id":"https:\/\/dbafox.com\/?p=339"},"wordCount":10,"publisher":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"articleSection":["Linux","MS SQL Server","Oracle Technical","PowerShell","Uncategorized"],"inLanguage":"en-GB"},{"@type":"WebPage","@id":"https:\/\/dbafox.com\/?p=339","url":"https:\/\/dbafox.com\/?p=339","name":"Display users using sp_who, sp_who2 - SQL Server - dbafox","isPartOf":{"@id":"https:\/\/dbafox.com\/#website"},"datePublished":"2014-11-21T10:00:20+00:00","dateModified":"2015-07-10T14:20:32+00:00","breadcrumb":{"@id":"https:\/\/dbafox.com\/?p=339#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbafox.com\/?p=339"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbafox.com\/?p=339#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbafox.com\/"},{"@type":"ListItem","position":2,"name":"Display users using sp_who, sp_who2 &#8211; SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/dbafox.com\/#website","url":"https:\/\/dbafox.com\/","name":"dbafox","description":"DBA Technical Information","publisher":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbafox.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb","name":"Ray Fox","logo":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/image\/"},"sameAs":["http:\/\/www.dbafox.com"]}]}},"_links":{"self":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/339","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=339"}],"version-history":[{"count":3,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/339\/revisions"}],"predecessor-version":[{"id":342,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/339\/revisions\/342"}],"wp:attachment":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=339"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=339"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=339"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}