T-SQL to create a SQL Server Database Inventory

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
----------------------------------------------------------

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s