May 5, 2015 at 3:56 pm
Comments posted to this topic are about the item Script to document SQL Instance Details
May 19, 2015 at 6:37 am
Thanks for the script. We'll give it a try.
May 19, 2015 at 9:31 am
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
May 19, 2015 at 11:35 am
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
February 8, 2016 at 4:03 am
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!
February 8, 2016 at 6:26 am
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)?
February 8, 2016 at 9:39 am
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.
February 8, 2016 at 10:48 am
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 '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
February 8, 2016 at 11:59 am
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.
March 8, 2016 at 4:33 pm
Thank you for the script.
March 17, 2016 at 6:55 pm
Pretty much easily available script
Thanks.
June 28, 2016 at 12:12 pm
Great tool for updating my inventory, I run it from the registered servers tab and get all my servers at once. FWIW I added
IF OBJECT_ID('tempdb..#SVer') IS NOT NULL DROP TABLE #SVer
GO
At the beginning incase I run it more than once and
CASE
WHEN (@@SERVERNAME like '%dev%')
THEN 'c. dev'
WHEN (@@SERVERNAME like '%test%')
THEN 'b.test'
WHEN (@@SERVERNAME like '%prod%')
THEN 'a. prod'
ELSE 'z. unknown'
END AS [Environment],
To show what environment the server is in- your naming convention may vary
August 11, 2016 at 10:45 am
Love this, thanks!!!
August 12, 2016 at 1:33 pm
Nice, thanks!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy