SQL Server Inventory Script

SQL Server Environment Inventory Script

This all-in-one SQL Server script gives you a complete overview of your SQL Server instance and host environment. It’s ideal for system documentation, audits, compliance checks, environment validation, or disaster recovery planning.

Purpose

The goal of this script is to extract and present key configuration details of the SQL Server instance, including:

What This Script Captures

SQL Server & Network Info

Cluster or RDP Host Name
SQL Server Name
IP Address
TCP Port Number

SQL Server Configuration

SQL Server Version (e.g., SQLServer2019)
Edition (e.g., Enterprise, Standard)
Product Version (e.g., 15.0.2000.5)
Service Pack / CU Level
Collation Setting
Installation Date

Service Accounts

SQL Server service account (via registry)
SQL Server Agent service account (via sys.dm_server_services)

Environment & Hardware Details

Operating System Distribution (e.g., Windows Server 2022)
Total Installed Memory (GB, rounded up)
Logical CPU count
Physical CPU count
Hyperthreading Ratio
Server Type (Physical or Virtual)
Virtual Machine Description (e.g., Hyper-V, VMware)

High Availability Info

Windows Cluster status

SQL Script:

DECLARE @ServiceaccountName VARCHAR(250);

EXEC master.dbo.xp_instance_regread
  N'HKEY_LOCAL_MACHINE',
  N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
  N'ObjectName',
  @ServiceaccountName OUTPUT,
  N'no_output';

SELECT
  CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR(17)) AS [Cluster Name/RDP],
  CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) AS [SQL Server Name],
  (SELECT local_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID) AS [IP Address],
  (SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID) AS [Port No],

  (SELECT CASE
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '11.%' THEN 'SQLServer2012'
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '12.%' THEN 'SQLServer2014'
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '13.%' THEN 'SQLServer2016'
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '14.%' THEN 'SQLServer2017'
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '15.%' THEN 'SQLServer2019'
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '16.%' THEN 'SQLServer2022'
    ELSE 'UNKNOWN'
  END) AS SQL_Name,

  CAST(SERVERPROPERTY('Edition') AS VARCHAR(200)) AS [Server Edition],
  CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS [Version],
  CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS [Service Pack],
  CAST(SERVERPROPERTY('Collation') AS VARCHAR(40)) AS [Collation],

  (SELECT CASE
    WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'Yes'
    WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'No'
    ELSE 'UNKNOWN'
  END) AS [IsClustered],

  (SELECT create_date FROM sys.server_principals WHERE sid = 0x010100000000000512000000) AS [SQLInstalledDate],
  @ServiceaccountName AS SQLServiceAcct,

  (SELECT service_account FROM sys.dm_server_services WHERE servicename LIKE 'SQL Server Agent%') AS SQLAgentAcct,
  (SELECT host_distribution FROM sys.dm_os_host_info) AS [OS],
  (SELECT CEILING(SUM(physical_memory_kb) / 1024.0 / 1024.0) FROM sys.dm_os_sys_info) AS [GB_OF_RAM],
  (SELECT cpu_count FROM sys.dm_os_sys_info) AS [LogicalCPUCount],
  (SELECT hyperthread_ratio FROM sys.dm_os_sys_info) AS [HyperthreadRatio],
  (SELECT cpu_count / hyperthread_ratio FROM sys.dm_os_sys_info) AS [PhysicalCPUCount],

  (SELECT CASE
    WHEN virtual_machine_type = 1 THEN 'Virtual'
    ELSE 'Physical'
  END FROM sys.dm_os_sys_info) AS [ServerType],

  (SELECT virtual_machine_type_desc FROM sys.dm_os_sys_info) AS [virtual_machine_type_desc];

Sample Output

Cluster Name/RDP	SQL Server Name	IP Address	Port No	SQL_Name	Server Edition	Version	Service Pack	Collation	IsClustered	SQLInstalledDate	SQLServiceAcct	SQLAgentAcct	OS	GB_OF_RAM	LogicalCPUCount	HyperthreadRatio	PhysicalCPUCount	ServerType	virtual_machine_type_desc
SQLNODE01	SQLNODE01\PROD01	1.1.**.45	1433	SQLServer2019	Enterprise Edition (64-bit)	15.0.4261.1	CU19	SQL_Latin1_General_CP1_CI_AS	Yes	2019-09-25 14:21:31.000	NT Service\MSSQLSERVER	NT Service\SQLSERVERAGENT	Windows Server 2022 Standard	128	16	2	8	Virtual	Hypervisor

Use Cases

Pre-migration documentation
Post-install validation
Monthly / Quarterly health reports
DR (Disaster Recovery) setup verification
Internal audits and compliance
Building a central SQL Server inventory dashboard

Script Notes

This script should be executed on each SQL Server you wish to inventory.
To collect information from multiple servers, consider using CMS (Central Management Server) or PowerShell to run the script remotely.