Server Information

 --Server Information

--SELECT SYSDATETIME() as 'ReportGenDate'

drop table #output;

drop table #diskinfo_t;

 

DECLARE @build varchar(15);

Declare @value nvarchar(15);

declare @osversion nvarchar(100);

DECLARE @AGPrimarynode varchar(15);

DECLARE @AGSecnode varchar(15);

DECLARE @Listner varchar(15);

DECLARE @DBNames VARCHAR(MAX);

DECLARE @Replica_names VARCHAR(MAX);

Declare @noofdbs nvarchar(15);

Declare @TotDbsize bigint;

Declare @TotPhysicalMemory nvarchar(20);

Declare @TotCPU nvarchar(20);

Declare @maxmemory sql_variant;

Declare @TDE nvarchar(1000);

Declare @Diskinfo_report nvarchar(1000);

Declare @collation nvarchar(50);

Declare @compatabilitylevel nvarchar(200);

Declare @AGgroup nvarchar(200);

 

--

declare @svrName varchar(255)

declare @sql varchar(400)

Declare @diskinfo nvarchar(200)

--by default it will take the current server name, we can the set the server name as well

set @svrName = @@SERVERNAME

set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

--creating a temporary table

CREATE TABLE #output

(line varchar(255))

--EXEC @diskinfo =xp_cmdshell @sql

--inserting disk name, total space and free space value in to temporary table

insert #output

EXEC xp_cmdshell @sql

select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as Drive_Name

   ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,

   (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'Drive_capacity_GB'

 into #diskinfo_t from #output

where line like '[A-Z][:]%'

order by Drive_Name

--script to drop the temporary table

--drop table #output

--declare @Diskinfo_report nvarchar (1000);

Declare @table1 table (id int , value varchar(1000));

insert into @table1 (id,value)

select 1,drive_name+'('+cast(Drive_capacity_GB as varchar(10)) +')' from #diskinfo_t

SELECT  @Diskinfo_report=(select distinct STUFF((SELECT ', ' + CAST(Value AS VARCHAR(10)) [text()] FROM @Table1

WHERE ID = t.ID FOR XML PATH('tr'), TYPE).value('.','NVARCHAR(MAX)'),1,2,' ') List_Output

FROM @Table1 t )

--select @Diskinfo_report  ;

 

select @TDE=name from sys.certificates where issuer_name like '%TDE%';

SELECT @TotCPU=cpu_count FROM sys.dm_os_sys_info;

SELECT @TotPhysicalMemory=FORMAT((physical_memory_kb /1024.00/1024.00) ,'N2') FROM sys.dm_os_sys_info;

select @TotDbsize=sum((mFiles.size)*8/1024)/1024 from SYS.MASTER_FILES mFiles INNER JOIN SYS.DATABASES dbs

      ON dbs.DATABASE_ID = mFiles.DATABASE_ID WHERE dbs.DATABASE_ID > 4;

--DB Names

SELECT @DBNames = COALESCE(@DBNames+', ' ,'') +name

FROM sys.databases where database_id>4;

--Compatability level

--select @compatabilitylevel=coalesce( @compatabilitylevel +', ' ,'') +name +compatibility_level , version_name =

--CASE compatibility_level

--    WHEN 65  THEN 'SQL Server 6.5'

--    WHEN 70  THEN 'SQL Server 7.0'

--    WHEN 80  THEN 'SQL Server 2000'

--    WHEN 90  THEN 'SQL Server 2005'

--    WHEN 100 THEN 'SQL Server 2008/R2'

--    WHEN 110 THEN 'SQL Server 2012'

--    WHEN 120 THEN 'SQL Server 2014'

--    WHEN 130 THEN 'SQL Server 2016'

--    WHEN 140 THEN 'SQL Server 2017'

--    WHEN 150 THEN 'SQL Server 2019'

--    WHEN 160 THEN 'SQL Server 2022'

--    ELSE 'new unknown - '+CONVERT(varchar(10),compatibility_level)

--END from sys.databases

 

 

select @noofdbs=count(*) from sys.databases where database_id>4;

SELECT @Replica_names = COALESCE(@replica_names+', ' ,'') +cs.replica_server_name

FROM sys.availability_groups ag

JOIN sys.dm_hadr_availability_group_states ags ON ag.group_id = ags.group_id

JOIN sys.dm_hadr_availability_replica_cluster_states cs ON ags.group_id = cs.group_id

JOIN sys.availability_replicas ar ON ar.replica_id = cs.replica_id

JOIN sys.dm_hadr_availability_replica_states rs  ON rs.replica_id = cs.replica_id

LEFT JOIN sys.availability_group_listeners al ON ar.group_id = al.group_id

 

select @AGgroup=name from sys.availability_groups

 

set @Listner=(SELECT al.dns_name AS 'Listener'

   FROM sys.availability_groups ag

JOIN sys.dm_hadr_availability_group_states ags ON ag.group_id = ags.group_id

JOIN sys.dm_hadr_availability_replica_cluster_states cs ON ags.group_id = cs.group_id

JOIN sys.availability_replicas ar ON ar.replica_id = cs.replica_id

JOIN sys.dm_hadr_availability_replica_states rs  ON rs.replica_id = cs.replica_id

LEFT JOIN sys.availability_group_listeners al ON ar.group_id = al.group_id where role_desc='PRIMARY')

set @build=(SELECT SUBSTRING(@@VERSION,CHARINDEX('build',@@VERSION,0),11) AS OSVersion)

set @value=(SELECT RIGHT(@build,5) AS OSVersion)

--print @value

SELECT @maxmemory=c.value FROM sys.configurations c WHERE c.[name] = 'max server memory (MB)';

select (SELECT SYSDATETIME()) as 'ReportGenDate',

             (select serverproperty('ComputerNamePhysicalNetBIOS')) as 'ServerName',

             (Select SERVERPROPERTY('MachineName')) as 'SQLV-Server',

             --@@SERVERNAME 'Server_Name' ,

             (SELECT @@servicename) as InstanceName,

             --SERVERPROPERTY('InstanceName') as Instance,

             --SERVERPROPERTY('ServerName') AS InstanceName, 

(select case @value

WHEN '3790:' THEN 'Windows Server 2003'

WHEN '3790:' THEN 'Windows Server 2003 R2'

WHEN '6003:' THEN 'Windows Server 2008'

WHEN '7601:' THEN 'Windows Server 2008 R2'  

WHEN '9200:' THEN 'Windows Server 2012'  

WHEN '9600:' THEN 'Windows Server 2012 R2'  

WHEN '14393'  THEN 'Windows Server 2016'

WHEN '17763'  THEN 'Windows Server 2019'

END as Windows_OS_Name

FROM sys.dm_os_windows_info a) OS_Versi,

               LEFT (@@VERSION, 35) as SQL_Version,

          SERVERPROPERTY('Edition') as Edition,

               SERVERPROPERTY('collation') AS SQLServerCollation,

               @TotPhysicalMemory as 'Total_Physical_RAM_GB',

               @maxmemory as SQL_Memory,

               @TotCPU as 'No_Of_Processors',

               @noofdbs as 'No of DBs',@TotDbsize as TOT_DBs_SIZE_GB,

               @TDE as 'TDE',

          --SERVERPROPERTY('ProductVersion') AS ProductVersion, 

               --SERVERPROPERTY('ProductLevel') as ProductLevel, /* RTM or SP1 etc*/

             Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else

          'STANDALONE' end as ServerType,

               CASE SERVERPROPERTY ('IsHadrEnabled') when 1 then 'AG' else

          'STANDALONE' end as 'AO STATUS',

               (SELECT @Replica_names +', '+'Listner_Name:  '+@Listner+', '+'AGGroup:  '+@AGgroup) as AG_Details,(select @DBNames) as Database_Names,(select @Diskinfo_report) as Diskinfo

               --@Diskinfo_report as Disk_info_GB into #temp

 

  go 

Note: If you face any errors related to sys.xp_cmdshell.

Please follow below steps:

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.

RECONFIGURE

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature.

RECONFIGURE

GO