The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,417 other followers

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 [WayBackServerProperty.

Since I absolutely love [WayBacksqlcmd, 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

Leave a Reply

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

 
%d bloggers like this: