Script to get the SQL Server Properties


You can execute the below script to get the SQL Server Properties.

create table #server(ID int,  Name  sysname null, Internal_Value int null, Value nvarchar(512) null)
insert #server exec master.dbo.xp_msver

declare @RegRootDir nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @RegRootDir OUTPUT

SELECT
(select Value from #server where Name = N'ProductName') AS [Product],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
(select Value from #server where Name = N'Language') AS [Language],
(select Value from #server where Name = N'Platform') AS [Platform],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
(select Internal_Value from #server where Name = N'ProcessorCount') AS [Processors],
(select Value from #server where Name = N'WindowsVersion') AS [OSVersion],
(select Internal_Value from #server where Name = N'PhysicalMemory') AS [PhysicalMemory],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
@RegRootDir AS [RootDirectory],
convert(sysname, serverproperty(N'collation')) AS [Collation]

drop table #server

Output

Product VersionString Language Platform Edition Processors OSVersion PhysicalMemory IsClustered RootDirectory Collation
Microsoft SQL Server 10.0.1600.22 English (United States) NT INTEL X86 Enterprise Edition 4 6.0 (6002) 3496 0 C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL SQL_Latin1_General_CP1_CI_AS


About these ads

One thought on “Script to get the SQL Server Properties

  1. Pingback: Blog Posts of the Week (18th - 24th November 2012) - The South Asia MVP Blog - Site Home - TechNet Blogs

Thanks for the comment, will get back to you soon... Jugal Shah

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 )

Google+ photo

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

Connecting to %s