Here is a T-SQL script used for inventory purpose on your SQL Server Instances.

Tested in: SQL Server 2016/SQL Server 2017

Script:

drop table if exists #t;
 
create table #t(
  ServerName varchar(128) default @@servername,
  ServerEdition sql_variant default serverproperty('edition'),
  ServerVersion sql_variant default @@version,
  ProductVersion sql_variant default serverproperty('productversion'),
  ProductLevel sql_variant default serverproperty('ProductLevel'),
  ServerCollation sql_variant default serverproperty('collation'),
  MachineName sql_variant default serverproperty('ComputerNamePhysicalNetBIOS'),
  DefaultDataPath sql_variant default serverproperty('InstanceDefaultDataPath'),
  DefaultLogPath sql_variant default serverproperty('InstanceDefaultLogPath'),
  IsClustered sql_variant default serverproperty('IsClustered'),
  IsFullTextInstalled sql_variant default serverproperty('IsFullTextInstalled'),
  IsIntegratedSecurityOnly sql_variant default CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
							WHEN 1 THEN 'Windows Authentication'   
							WHEN 0 THEN 'Windows and SQL Server Authentication'   
							END,
  cpu_count int,
  hyperthread_ratio int,
  Physical_Memory_GB Bigint,
  max_workers_count Bigint,
  sqlserver_start_time datetime,
  cores_per_socket bigint,
  numa_node_count bigint,
  --Configuration Details
  [Ad Hoc Distributed Queries] sql_variant,[xp_cmdshell] sql_variant,
  [optimize for ad hoc workloads] sql_variant,[backup compression default] sql_variant,
  [blofcked process threshold (s)] sql_variant,[default trace enabled] sql_variant,
  [clr enabled] sql_variant,[max server memory (MB)]sql_variant,
  [min server memory (MB)] sql_variant,[max degree of parallelism] sql_variant,
  [cost threshold for parallelism] sql_variant,[max text repl size (B)] sql_variant,
  [show advanced options] sql_variant
);

insert into #t(cpu_count,hyperthread_ratio,Physical_Memory_GB,max_workers_count,sqlserver_start_time,cores_per_socket,numa_node_count,
[Ad Hoc Distributed Queries],[xp_cmdshell],[optimize for ad hoc workloads],[backup compression default]
    ,[blofcked process threshold (s)],[default trace enabled],[clr enabled],[max server memory (MB)],[min server memory (MB)]
    ,[max degree of parallelism],[cost threshold for parallelism],[max text repl size (B)],[show advanced options])
 
select cpu_count,hyperthread_ratio,((Physical_Memory_kb/1024.0)/1024.0),max_workers_count,
sqlserver_start_time,cores_per_socket,numa_node_count,
B.*
from sys.dm_os_sys_info A
outer apply ( Select Max([Ad Hoc Distributed Queries]) [Ad Hoc Distributed Queries],
        Max([xp_cmdshell]) [xp_cmdshell] ,
        Max([optimize for ad hoc workloads]) [optimize for ad hoc workloads],
        Max([backup compression default]) [backup compression default],
        Max([blofcked process threshold (s)]) [blocked process threshold (s)],
        Max([default trace enabled]) [default trace enabled],
        Max([clr enabled]) [clr enabled],
        Max([max server memory (MB)]) [max server memory (MB)],
        Max([min server memory (MB)]) [min server memory (MB)],
        Max([max degree of parallelism]) [max degree of parallelism],
        Max([cost threshold for parallelism])[cost threshold for parallelism],
        Max([max text repl size (B)]) [max text repl size (B)],
        Max([show advanced options]) [show advanced options]
 From sys.configurations 
 PIVOT (MAX(Value_in_use) for Name in([Ad Hoc Distributed Queries],[xp_cmdshell],[optimize for ad hoc workloads],[backup compression default]
    ,[blofcked process threshold (s)],[default trace enabled],[clr enabled],[max server memory (MB)],[min server memory (MB)]
    ,[max degree of parallelism],[cost threshold for parallelism],[max text repl size (B)],[show advanced options])) as PVT) B
 
Select * From #t

I would really love to hear from you what else you would like to see to this list?

See Also:

https://sqlzealots.com/2020/04/26/sql-server-inventory-queries-sql-server-databases/

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!