SQL Server: obtain ServerProperty values using sqlcmd
Posted by jpluimers on 2010/10/20
When running different SQL server versions and editions, you often want to know what a certain instance is about.
For that, SQL Server T-SQL provides [WayBack] ServerProperty.
Since I absolutely love [WayBack] sqlcmd, I extended my set of SQL Server batch files with this one showing you some of the [WayBack] ServerProperty values:
call sql-find-sqlcmd.bat
call sql-find-instance.bat
%sqlcmd% -S %sqlinstance% -E -Q "select cast(serverproperty('MachineName') as varchar(20)) MachineName, cast(serverproperty('InstanceName') as varchar(15)) Instance, cast(serverproperty('ProductVersion') as varchar(12)) ProductVersion, cast(serverproperty('ProductLevel') as varchar(5)) ProductLevel, cast(serverproperty('Edition') as varchar(20)) Edition, cast(serverproperty('BuildClrVersion') as varchar(15)) CLR, cast(serverproperty('LicenseType') as varchar(15)) LicenseType, cast(serverproperty('NumLicenses') as varchar(5)) NumLicenses"
That looks complex, so lets split the actual SQL statement across a couple of lines:
select
cast(serverproperty('MachineName') as varchar(20)) MachineName,
cast(serverproperty('InstanceName') as varchar(15)) Instance,
cast(serverproperty('ProductVersion') as varchar(12)) ProductVersion,
cast(serverproperty('ProductLevel') as varchar(5)) ProductLevel,
cast(serverproperty('Edition') as varchar(20)) Edition,
cast(serverproperty('BuildClrVersion') as varchar(15)) CLR,
cast(serverproperty('LicenseType') as varchar(15)) LicenseType,
cast(serverproperty('NumLicenses') as varchar(5)) NumLicenses"
For MSDE, it shows this:
MachineName Instance ProductVersion ProductLevel Edition CLR LicenseType NumLicenses -------------------- --------------- -------------- ------------ -------------------- --------------- --------------- ----------- W2K3-VM STARTEAM 8.00.679 SP2 Desktop Engine NULL DISABLED NULL
For SQL Server 2005 Express, it shows this:
MachineName Instance ProductVersion ProductLevel Edition CLR LicenseType NumLicenses -------------------- --------------- -------------- ------------ -------------------- --------------- --------------- ----------- XP-SQL2005 SQLEXPRESS 9.00.4035.00 SP3 Express Edition v2.0.50727 DISABLED NULL
For SQL Server 2008, it shows this:
MachineName Instance ProductVersion ProductLevel Edition CLR LicenseType NumLicenses -------------------- --------------- -------------- ------------ -------------------- --------------- --------------- ----------- VM-W2K3-SQL2008 NULL 10.0.1600.22 RTM Standard Edition v2.0.50727 DISABLED NULL
Related: [WayBack] How can I tell what edition of SQL Server runs on the machine? – Stack Overflow
–jeroen
The Wiert Corner - irregular stream of stuff 
Leave a comment