Problem
Have you ever wondered where SQL Server Management Studio (SSMS) gets its data from? Or 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?
Solution
One simple way of figuring this out is to use Profiler to capture the queries that are being executed. 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.
Launch Profiler
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 Windows menu
- Launch Profiler from SQL Server Management Studio from Tools > SQL Server Profiler
Create a Trace
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
- Click on the Column Filters… button and enter “Microsoft SQL Server Management Studio%” in the Like section and click OK.
- Then click Run
Profiler Output Showing SSMS Data Queries
The following screen shows the queries that are being used by SSMS. If you click around in SSMS more queries will show in the output. Once you have the data you need, you can pause or stop the trace from running.
Based on the output from Profiler, the below query from SSMS gets a list of databases. You can copy and paste the SQL into a query window and execute.
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)
begin
insert into #tmp_db_hadr_dbrs select group_database_id, synchronization_state, is_local from master.sys.dm_hadr_database_replica_states
end
SELECT dtb.name AS [Database_Name],
''Server[@Name='' + quotename(CAST(serverproperty(N''Servername'') AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(dtb.name,'''''''') + '']'' 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],
dtb.name AS [Database_DatabaseName2],
dtb.name 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 dtb.name 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)',
@_msparam_0=N'0',
@_msparam_1=N'0' Next Steps
- Setup Profiler to capture events and then start clicking away in SSMS to see what is actually happening. You can then learn how to write your own queries from the system tables and views.
- If there is a lot of activity on the server, use additional filters to capture data for specific databases or users.
Greg Robidoux has been working with databases for 35+ years with extensive hands on SQL Server experience from version 6.5 to 2025. He has authored over 250 technical articles and delivered several presentations online and at various conventions. Greg is also the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server.
