How does SQL Server Management Studio get its data

By:   |   Comments (4)   |   Related: > SQL Server Management Studio


Have you ever wondered where SQL Server Management Studio (SSMS) gets its information from and how you can retrieve the same data without using SSMS?  Well it's not a big secret. Most of the information that is displayed in the GUI is based on information gathered from queries, but what queries?  If you have ever looked at the system tables to try to figure this out, it is not as straight forward as you think.  So how do you find out what queries SSMS is using?


One simple way of figuring this out is to use Profiler to capture the queries that are being executed and then you can use these same queries to gather the same information.  It is a pretty straight forward process to use Profiler even if you have never used Profiler before.

The first step is to launch Profiler, this can be done one of two ways:

  • Run Profiler (profiler.exe) either from the command line or from the menu
  • or
  • Launch Profiler from SQL Server Management Studio from Tools > SQL Server Profiler

Once you have Profiler launched you need to create a new trace.  To do this follow the steps below.

  • File > New Trace...
  • Connect to the server and database
  • Go to the Events Selection tab and select the following Events and Columns
sql profiler events selection
  • Click on the Column Filters... button and enter "Microsoft SQL Server Management Studio%" in the Like section and click OK.
sql profiler filters
  • Then click Run
sql profiler run

The following screen shows the queries that are being used by SSMS once you start clicking around in SSMS.  Once you have the data you can either pause or stop the trace from running.

sql profiler event output

Based on the output from Profiler above we can see the below query that SSMS uses to get a list of databases.

EXEC Sp_executesql 
  N'create table #tmp_db_hadr_dbrs (group_database_id uniqueidentifier, synchronization_state tinyint, is_local bit)
  declare @HasViewPermission int
  select @HasViewPermission = HAS_PERMS_BY_NAME(null, null, ''VIEW SERVER STATE'')
  if (@HasViewPermission = 1)
  insert into #tmp_db_hadr_dbrs select group_database_id, synchronization_state, is_local from master.sys.dm_hadr_database_replica_states
  SELECT AS [Database_Name], 
  ''Server[@Name='' + quotename(CAST(serverproperty(N''Servername'') AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(,'''''''') + '']'' AS [Database_Urn], 
  dtb.containment AS [Database_ContainmentType], 
  dtb.recovery_model AS [Database_RecoveryModel], 
  ISNULL(suser_sname(dtb.owner_sid),'''') AS [Database_Owner],
  case when dtb.collation_name is null then 0x200 else 0 end |         
  case when 1 = dtb.is_in_standby then 0x40 else 0 end |
  case dtb.state when 1 then 0x2
  				 when 2 then 0x8         
  				 when 3 then 0x4         
  				 when 4 then 0x10         
  				 when 5 then 0x100         
  				 when 6 then 0x20         
  				 else 1 end AS [Database_Status], 
  dtb.compatibility_level AS [Database_CompatibilityLevel], 
  ISNULL(dmi.mirroring_role,0) AS [Database_MirroringRole], 
  ISNULL(dmi.mirroring_state + 1, 0) AS [Database_MirroringStatus], 
  dbrs.synchronization_state AS [Database_AvailabilityDatabaseSynchronizationState], 
  0 AS [Database_HasMemoryOptimizedObjects], 
  CAST(dtb.is_remote_data_archive_enabled AS bit) AS [Database_RemoteDataArchiveEnabled], 
  CAST(case when SERVERPROPERTY(''EngineEdition'') = 6 then cast(1 as bit) else cast(0 as bit) end AS bit) AS [Database_IsSqlDw], 
  dtb.recovery_model AS [RecoveryModel], 
  dtb.user_access AS [UserAccess], 
  dtb.is_read_only AS [ReadOnly], AS [Database_DatabaseName2], AS [Database_DatabaseName3] 
  FROM master.sys.databases AS dtb 
  LEFT OUTER JOIN sys.database_mirroring AS dmi 
  	ON dmi.database_id = dtb.database_id 
  LEFT OUTER JOIN #tmp_db_hadr_dbrs AS dbrs 
  	ON dtb.group_database_id = dbrs.group_database_id 
  	and dbrs.is_local = 1
  WHERE (CAST(case when in (''master'',''model'',''msdb'',''tempdb'') then 1 else dtb.is_distributor end AS bit)=@_msparam_0 
  and CAST(isnull(dtb.source_database_id, 0) AS bit)=@_msparam_1)
  ORDER BY [Database_Name] ASC
  drop table #tmp_db_hadr_dbrs', 
  N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)', 
Next Steps
  • Setup Profiler to capture events and then start clicking away in SSMS to see what is actually happening behind the scenes to help you learn how to write your own queries from the system tables and views.
  • If there is a lot of activity on the server, you can use additional filters to capture data for specific databases or users.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, March 11, 2020 - 3:21:28 PM - Greg Robidoux Back To Top (85067)

Thanks Solomon.  I am sure I read this other article in the past and completely forgot about this method.

Thanks for bringing it up.


Wednesday, March 11, 2020 - 2:26:20 PM - Solomon Rutzky Back To Top (85065)

Hi Greg. Most of the time there's no need for either Extended Events or SQL Server Profiler, at least not for anyone using SSMS 17 or newer. Most of the Object Explorer queries should be provided in the "Output" window (Control-O). In fact, I just found an article on this site that does a really good job of describing that feature:

SQL Server Management Studio Output Option for Object Explorer Queries and Telemetry Data

Take care,

Wednesday, March 4, 2020 - 9:09:36 AM - Greg Robidoux Back To Top (84918)

Thanks Vladimir.  I will try to update this to show how to do using Extended Events as well.


Tuesday, March 3, 2020 - 7:07:36 PM - Vladimir D Sotirov Back To Top (84907)

Profile is an old tool. Better explain how to do the same using extended events.

get free sql tips
agree to terms