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 #tI 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!
2 thoughts on “SQL Server Inventory Queries – SQL Server Instances”