Script to document SQL Instance Details

Question

Post reply

Script to document SQL Instance Details

Subhash R Amanaganti

SSC Veteran

Points: 262
More actions
May 5, 2015 at 3:56 pm

#303601

Comments posted to this topic are about the item Script to document SQL Instance Details

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 1

akljfhnlaflkj

SSC Guru

Points: 76202

May 19, 2015 at 6:37 am

#1798987

Thanks for the script. We'll give it a try.

kwitzell SSCrazy Points: 2470 More actions · Answer 2

Very handy script thanks!

I did add the following: serverproperty('productlevel')ServicePack,

after the line: end as VersionName,

Since I can't remember what service pack my sql servers are on 🙂

Subhash R Amanaganti SSC Veteran Points: 262 More actions · Answer 3

Hi kwitzell,

I usually refer to http://sqlserverbuilds.blogspot.com/ to cross check what SP's and CU's installed on the instance based on the version string. But you made a good point on reviewing SP's in one shot.

Thanks

Subhash

Alberto Turelli SSC-Addicted Points: 420 More actions · Answer 4

Nice script!

I just added another option to the VersionName CASE statement to make the script aware of SQL Server 2016:

when convert(varchar(100),SERVERPROPERTY(N'ProductVersion')) like '13.0%' then 'SQL Server 2016'

and removed a duplicate option for SQL Server 2008.

Thanks!

Konstantin Taranov SSCertifiable Points: 6498 More actions · Answer 5

Why are not use awesome Glenn Alan Berry (http://www.sqlskills.com/blogs/glenn/) diagnostic queries (https://github.com/ktaranov/sqlserver-kit/tree/master/Scripts)?

C F-215929 SSC Journeyman Points: 87 More actions · Answer 6

I get this error when running on a multi-instance multi server cluster.

Msg 512, Level 16, State 1, Line 12

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

SenseiTim Valued Member Points: 56 More actions · Answer 7

Here's a modified version where I added the port number SQL Server is running on (we usually change it from the default 1433).

create table #SVer(ID int, Name sysname, Internal_Value int, Value nvarchar(512))

insert #SVer exec master.dbo.xp_msver

declare @SmoRoot nvarchar(512)

DECLARE @SN NVARCHAR(128)

DECLARE @sa NVARCHAR(128)

DECLARE @tcp_port nvarchar(5)

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'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',N'ObjectName', @SN OUTPUT;

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'SYSTEM\CurrentControlSet\services\MSSQLSERVER',N'ObjectName', @sa OUTPUT;

EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',N'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',N'TcpPort', @tcp_port OUTPUT

SELECT

@@SERVERNAME as InstanceName,

(select Value from #SVer where Name = N'ProductName') AS [Product],

SERVERPROPERTY(N'ProductVersion') AS [VersionString],

case when convert(varchar(100), SERVERPROPERTY(N'ProductVersion')) like '12.%' then 'SQL Server 2014'

when convert(varchar(100), SERVERPROPERTY(N'ProductVersion')) like '11.%' then 'SQL Server 2012'

when convert(varchar(100),SERVERPROPERTY(N'ProductVersion')) like '10.5%' then 'SQL Server 2008R2'

when convert(varchar(100),SERVERPROPERTY(N'ProductVersion')) like '10.0%' then 'SQL Server 2008'

when convert(varchar(100),SERVERPROPERTY(N'ProductVersion')) like '9.0%' then 'SQL Server 2005'

else 'Not Found'

end as VersionName,

--(select Value from #SVer where Name = N'Language') AS [Language],

(select Value from #SVer where Name = N'Platform') AS [Platform],

CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],

(select Internal_Value from #SVer where Name = N'ProcessorCount') AS [Processors],

(select Value from #SVer where Name = N'WindowsVersion') AS [OSVersion],

(select Internal_Value from #SVer where Name = N'PhysicalMemory') AS [PhysicalMemory_In_MB],

(SELECT value_in_use FROM sys.configurations WHERE name like '%max server memory%')AS max_server_memory_MB,

(SELECT value_in_use FROM sys.configurations WHERE name like '%min server memory%')AS min_server_memory_MB,

case when CAST(SERVERPROPERTY('IsClustered') AS bit) =1 then 'YES'

else 'NO' END

AS [IsClustered],

case when CAST(SERVERPROPERTY('IsClustered') AS bit)= 1 then (select serverproperty('ComputerNamePhysicalNetBIOS'))

else NULL END as Active_Node_Name,

(SELECT NodeName

FROM sys.dm_os_cluster_nodes where NodeName !=(select serverproperty('ComputerNamePhysicalNetBIOS'))) as Passive_Node_Name,

@SmoRoot AS [RootDirectory],

@sa as [SQLService_Account],

@SN as [SQLAgent_Account],

convert(sysname, serverproperty(N'collation')) AS [Collation],

@tcp_port as [Port]

--Add for versions greater than 2005

,(SELECT sqlserver_start_time FROM sys.dm_os_sys_info) as SQLServer_Start_Time

drop table #SVer

C F-215929 SSC Journeyman Points: 87 More actions · Answer 8

Thanks for the tip but still receiving error.

(20 row(s) affected)

RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.'

Msg 22001, Level 1, State 1

Msg 512, Level 16, State 1, Line 14

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.