Scripts to prepare SQL Server Inventory

Posted by Deepak Kumar | Posted in Scripts, SQL DBA, SQL Dev, Uncategorized | Posted on 21-12-2010

3

You never know, when you may need to rebuild a SQL sever with exact properties and configurations. Although there are dozen of 3rd party tools and software available in market to prepare enterprise level SQL Server inventory hosted in your environment like Microsoft Assessment and Planning (MAP), but keeping your own DBA script handy is big pleasure. Here is my own version of script that can bring you many hidden properties of SQL Server that are worth storing along with SQL inventory.

 

SET NOCOUNT ON
-- FIND SQL SERVER COLLATION
declare @SerCollation varchar(70)
select @SerCollation=convert(sysname, serverproperty(N'collation'))
PRINT 'SQL Server Default Collation is: '+@SerCollation
 
-- FIND SQL SERVER AND AGENT SERVICE ACCOUNT INFORMATION
PRINT 'SQL & Agent Serice Account Information: '
DECLARE @SQLserviceaccount varchar(50), @AgentServiceaccount varchar(50)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER', N'ObjectName',
@SQLserviceaccount OUTPUT, N'no_output'
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT', N'ObjectName',
@AgentServiceaccount OUTPUT, N'no_output'
SELECT @SQLserviceaccount as [SQLServer_ServiceAccount], @AgentServiceaccount as SQLAgent_ServiceAccount
 
-- FIND SQL SERVER AUTHENTICATION, AUDIT MODE & INSTALLATION PATH
PRINT 'SQL Server Authentication, Audit Mode & Installation path: '
declare @SmoLoginMode int, @SmoRoot nvarchar(512), @SmoAuditLevel int
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', 
N'SQLPath', @SmoRoot OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'LoginMode', @SmoLoginMode OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'AuditLevel', @SmoAuditLevel OUTPUT
 
SELECT(case when @SmoLoginMode =1 then 'Windows Authentication'
else 'Mixed Mode Authentication'  END) AS [LoginMode],
(case When @SmoAuditLevel=0 then 'None.'
When @SmoAuditLevel=1 then 'Successful Logins Only'
When @SmoAuditLevel=2 then 'Failed Logins Only'
When @SmoAuditLevel=3 then 'Both Failed and Successful Logins Only'
else 'N/A' END) AS [AuditLevel],
@SmoRoot as [SQLInstallation Location]
 
-- FIND LICENSE AND PAGE FILE INFORMAITON
PRINT 'License and page file information: '
Declare @version varchar(47), @CDKey varchar(40), @PageFile varchar(50)
Select @version = @@version
create table #PageFileDetails (data varchar(500))
insert into #PageFileDetails  exec master.dbo.xp_cmdshell 'wmic pagefile list /format:list'
select @PageFile=rtrim(ltrim(data)) from #PageFileDetails where data like 'AllocatedBaseSize%'
drop table #PageFileDetails
 
If charindex('2000',@version,1)>0
BEGIN
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Microsoft SQL Server\80\Registration',
@value_name='CD_KEY', @Value=@CDKey OUTPUT
SELECT 'SQL 2000' AS SQLVersion,
CONVERT(char(40), SERVERPROPERTY('ServerName')) AS SQL_Service_Name, @PageFile AS PageFile,
CONVERT(char(50), SERVERPROPERTY('Edition'))AS SQLEdition,
CONVERT(char(20), SERVERPROPERTY('productversion')) AS ProductVersion,
CONVERT(char(20), SERVERPROPERTY('LicenseType'))AS License_Type,
CONVERT(char(20), SERVERPROPERTY('NumLicenses')) AS Number_Of_Licenses,
@CDKey AS CDKey
END
 
Else If charindex('2008',@version,1)>0
BEGIN
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\Setup',
@value_name='ProductID', @value=@CDKey OUTPUT
SELECT 'SQL 2008' AS SQLVersion,
CONVERT(char(40), SERVERPROPERTY('ServerName')) AS SQL_Service_Name, @PageFile AS PageFile,
CONVERT(char(50), SERVERPROPERTY('Edition'))AS SQLEdition,
CONVERT(char(20), SERVERPROPERTY('productversion')) AS ProductVersion,
CONVERT(char(20), SERVERPROPERTY('LicenseType'))AS License_Type,
CONVERT(char(20), SERVERPROPERTY('NumLicenses')) AS Number_Of_Licenses,
@CDKey AS CDKey
END
 
Else If charindex('2008 R2',@version,1)>0
BEGIN
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Microsoft SQL Server\150\Tools\Setup',
@value_name='ProductID', @value=@CDKey OUTPUT
SELECT 'SQL 2008 R2' AS SQLVersion,
CONVERT(char(40), SERVERPROPERTY('ServerName')) AS SQL_Service_Name, @PageFile AS PageFile,
CONVERT(char(50), SERVERPROPERTY('Edition'))AS SQLEdition,
CONVERT(char(20), SERVERPROPERTY('productversion')) AS ProductVersion,
CONVERT(char(20), SERVERPROPERTY('LicenseType'))AS License_Type,
CONVERT(char(20), SERVERPROPERTY('NumLicenses')) AS Number_Of_Licenses,
@CDKey AS CDKey
END
Else
SELECT @version AS SQLVersion,
CONVERT(char(40), SERVERPROPERTY('ServerName')) AS SQL_Service_Name, @PageFile AS PageFile,
CONVERT(char(50), SERVERPROPERTY('Edition'))AS SQLEdition,
CONVERT(char(20), SERVERPROPERTY('productversion')) AS ProductVersion,
CONVERT(char(20), SERVERPROPERTY('LicenseType'))AS License_Type,
CONVERT(char(20), SERVERPROPERTY('NumLicenses')) AS Number_Of_Licenses

Comments (3)

Nice Script. I’ll help. Thanks, mikeF

Nice script. I’ll help. Thanks – mikeF

I worked on a similar solution to collect Windows and SQL Server inventory by gathering scripts from SQL Family and released it as a free tool at http://crazydba.com. The tool uses powershell to grab data from all servers and stores it in SQL tables and can be viewed using SSRS reports. Can you please check it and give me any suggestions to improve it?

Thnx

Write a comment

*

© 2010 SQLKnowledge.com Increase your website traffic with Attracta.com