Problem
I had to work on Inventory Management System and log details for various SQL instances in my support. The old way to do that was to log into every instance check the details and copy paste that into excel.
Well I am a lazy person and I had to find an easier way to do that, I can’t remote desktop 200 instances and find/copy that detail one by one, that is so not me. I decided to create a script to find that detail using t-sql. Now next part was to run it on all the instances. Now for that Microsoft has been clever enough to provide us with “REGISTERED SERVERS”.
You just have to register your servers in the list and open query to that containing folder.
This t-sql script will generate results with the following details:
Solution
This t-sql script will log the following details:
1. Is it Cluster or Standalone Instance
2. Server Type (Microsoft Windows Physical Server or Virtual Server )
3. Hostname / SQL Cluster Name
4. Server IP / SQL Virtual IP
5. DB Technology
6. SQL Instance Name
7. SQL Port Number
8. SQL Service Account
9. Version
10. SQL Edition
11. SQL Version Number
12. Service Pack
13. Database(s) Name
14. .mdf file location (Primary Data file Location)
15. .ldf file location (Transaction Log file Location)
16. Tempdb data file Location
17. Tempdb log file Location
18. Root Directory ( Where SQL Binaries are installed )
19. SQL Error Log Path
20. Prod/Dev/QA/UAT – Well this one is for you to fill!!
exec sp_configure 'show advanced options', 1;
RECONFIGURE with override;
------------------------------------------------
SET NOCOUNT ON
create table #spconfig(s_name varchar(50), mini bigint, maxi bigint, config_value bigint, run_value bigint)
insert into #spconfig(s_name, mini, maxi,config_value, run_value) execute sp_configure
declare @testvaluec as int
declare @testvaluer as int
set @testvaluec = (select config_value from #spconfig where s_name like '%xp_cmdshell%' )
set @testvaluer = (select run_value from #spconfig where s_name like '%xp_cmdshell%' )
---------------------------------------------------------------------------------------------------------------
---------------------------------------------CONDITION 1
IF (@testvaluec =0 AND @testvaluer = 0)
----------------------------------------------BLOCK A
BEGIN
---------------------------------------------------
exec sp_configure 'show advanced options', 1;
RECONFIGURE with override;
exec sp_configure 'xp_cmdshell', 1;
RECONFIGURE with override;
---------------------------------------------------------------
declare @rootdir nvarchar(1000)
exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\Setup',
N'SQLPath', @rootdir OUTPUT
declare @rootdir1 nvarchar(1000)
exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData', @rootdir1 OUTPUT
declare @rootdir2 nvarchar(1000)
exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog',@rootdir2 OUTPUT
declare @ErrLogPath1 nvarchar(500)
exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters',
N'SqlArg1', @ErrLogPath1 OUTPUT
DECLARE @DBEngineLogin VARCHAR(100)
EXECUTE master.dbo.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
@value_name = N'ObjectName',
@value = @DBEngineLogin OUTPUT
DECLARE @tmpNewValue TABLE (newvalue varchar(500))
INSERT INTO @tmpNewValue EXEC xp_cmdshell 'systeminfo | findstr /c:"System Manufacturer"'
DECLARE @localVariable varchar(500)
SET @localVariable = (SELECT top 1 rtrim(ltrim(newvalue)) FROM @tmpNewValue )
--case when @localVariable like '%VMware%' OR @localVariable like '%hyper%'Then 'Virtual' else 'Physical' end as 'ServerType'
select
Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else 'STANDALONE' end as 'ServerType',
case when @localVariable like '%VMware%' OR @localVariable like '%hyper%'Then 'Virtual' else 'Physical' end as 'ServerType(PhysicalorVirtual)',
SERVERPROPERTY('MachineName') as 'MachineName',
(SELECT LOCAL_NET_ADDRESS AS 'IPAddressOfSQLServer'
FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID) as IPAddress,'MSSQL' as 'Technology',
@@servername as 'InstanceName',
(SELECT local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID) as 'port',
@DBEngineLogin as 'ServiceAccount',
left(@@VERSION, CHARINDEX(' - ',@@version)-1) as 'Version',
SERVERPROPERTY ('Edition') as 'Edition',
SERVERPROPERTY('productversion') 'VersionNumber',
SERVERPROPERTY ('productlevel') as 'ServicePack',
(select stuff((SELECT ', '+ cast (ROW_NUMBER() Over(order by dbid) as varchar)+'.'+ UPPER(name)
FROM sys.sysdatabases FOR XML PATH ('')), 1, 1, '') as 'DB') as 'Databases',
@rootdir1 as 'data', @rootdir2 as 'log',
(select top 1 filename from sys.sysaltfiles where filename like '%tempdb%' and filename like '%.mdf') as 'tempdbdata',
(select top 1 filename from sys.sysaltfiles where filename like '%tempdb%' and filename like '%.ldf') as 'tempdblog',
@rootdir as 'root',
SUBSTRING(substring(@ErrLogPath1, 1, len(@ErrLogPath1) - charindex('\', reverse(@ErrLogPath1))),3,500) as 'ErrorLogPath'
---------------------------------------------------------------
exec sp_configure 'show advanced options', 1;
RECONFIGURE with override;
exec sp_configure 'xp_cmdshell', 0;
RECONFIGURE with override;
exec sp_configure 'show advanced options', 0;
Reconfigure with override;
----------------------------------------------------------------
Drop Table #spconfig
END
----------------------------------------------------------------
---------------------------------------------CONDITION 2
ELSE IF (@testvaluec =1 AND @testvaluer = 1)
----------------------------------------------------------BLOCK B
BEGIN
-------------------------------------------------------------------
declare @rootdir3 nvarchar(500)
exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\Setup',
N'SQLPath', @rootdir3 OUTPUT
declare @rootdir4 nvarchar(500)
exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData', @rootdir4 OUTPUT
declare @rootdir5 nvarchar(500)
exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog', @rootdir5 OUTPUT
DECLARE @DBEngineLogin1 VARCHAR(100)
EXECUTE master.dbo.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
@value_name = N'ObjectName',
@value = @DBEngineLogin1 OUTPUT
declare @ErrLogPath nvarchar(500)
exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters',
N'SqlArg1', @ErrLogPath OUTPUT
DECLARE @tmpNewValue1 TABLE (newvalue varchar(500))
INSERT INTO @tmpNewValue1 EXEC xp_cmdshell 'systeminfo | findstr /c:"System Manufacturer"'
DECLARE @localVariable1 varchar(500)
SET @localVariable1 = (SELECT top 1 rtrim(ltrim(newvalue)) FROM @tmpNewValue1 )
--case when @localVariable like '%VMware%' OR @localVariable like '%hyper%'Then 'Virtual' else 'Physical' end as 'ServerType'
select
Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else 'STANDALONE' end as 'ServerType',
case when @localVariable like '%VMware%' OR @localVariable like '%hyper%'Then 'Virtual' else 'Physical' end as 'ServerType(PhysicalorVirtual)',
SERVERPROPERTY('MachineName') as 'MachineName',
(SELECT LOCAL_NET_ADDRESS AS 'IPAddressOfSQLServer'
FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID) as IPAddress,'MSSQL' as 'Technology',
@@servername as 'InstanceName',
(SELECT local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID) as 'port',
@DBEngineLogin1 as 'ServiceAccount',
left(@@VERSION, CHARINDEX(' - ',@@version)-1) as 'Version',
SERVERPROPERTY ('Edition') as 'Edition',
SERVERPROPERTY('productversion') 'VersionNumber',
SERVERPROPERTY ('productlevel') as 'ServicePack',
(select stuff((SELECT ', '+ cast (ROW_NUMBER() Over(order by dbid) as varchar)+'.'+ UPPER(name)
FROM sys.sysdatabases FOR XML PATH ('')), 1, 1, '') as 'DB') as 'Databases',
@rootdir4 as 'data', @rootdir5 as 'log',
(select top 1 filename from sys.sysaltfiles where filename like '%tempdb%' and filename like '%.mdf') as 'tempdbdata',
(select top 1 filename from sys.sysaltfiles where filename like '%tempdb%' and filename like '%.ldf') as 'tempdblog',
@rootdir3 as 'root',
SUBSTRING(substring(@ErrLogPath, 1, len(@ErrLogPath) - charindex('\', reverse(@ErrLogPath))),3,500) as 'ErrorLogPath'
-------------------------------------------------------------------
exec sp_configure 'show advanced options', 0;
Reconfigure with override;
---------------------------------------------------------
drop table #spconfig
END
----------------------------------------------------------
The 'Cloud' Workbook 