使用 T-SQL 深入了解 SQL Server 的基本資料

EXECUTE xp_regread @rootkey='HKEY_LOCAL_MACHINE',
                   @key='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',
                   @value_name='MSSQLSERVER'

Value	Data
MSSQLSERVER	MSSQL10_50.MSSQLSERVER

 

可使用 SELECT SERVERPROPERTY() 得到 Server 的相關資料。
如：

SELECT SERVERPROPERTY('ServerName') SELECT SERVERPROPERTY('InstanceName') SELECT SERVERPROPERTY('productversion') SELECT SERVERPROPERTY ('productlevel') SELECT SERVERPROPERTY ('edition')	GC-IT-N01 NULL 10.50.1600.1 RTM Standard Edition (64-bit)
SELECT @@servername SELECT @@VERSION	GC-IT-N01 Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

 

SELECT SERVERPROPERTY('InstanceName') 的資料，來自於：
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL

 

SELECT SERVERPROPERTY('productversion') 的資料，來自於：
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\ClientSetup\CurrentVersion

 

SQL Server Instances

In order to really understand what versions of SQL Server are installed, you have to understand SQL Server Instances. Each instance of SQL Server operates independently and contains its own collection of databases and settings. There are two types of instances:

Default Instance	a single instance named MSSQLSERVER. You connect to this instance using only the server name (ex: localhost)
Named Instance	one or more instances. You connect to these instances uing the server name + instance name (ex: localhost\SQLEXPRESS)

 

It's possible have only one instance installed on your machine. But it's also possible to have many instances. And each of those instances could be a different version of SQL Server!

To understand your scenario, use the SQL Server Configuration Manager tool that installs with both SQL Server 2005 and 2008.
On the start menu, expand the "Microsoft SQL Server [YOURVERSION]" menu, and then expand the "Configuration Tools" submenu. Launch the SQL Server Configuration Manager tool.
Here is a screen shot to use as an example:

點選右側 SQL Server (MSSQLSERVER) 選項後，按右鍵／內容，可以看到如下結果：

 

對於軟體版本的進一步訊息，可參考

 

更多資訊可參考：
http://technet.microsoft.com/en-us/library/ms174396.aspx
http://learningsqlserver.wordpress.com/2011/01/21/what-version-of-sql-server-do-i-have/
SQLSecurity.com http://www.sqlsecurity.com/faqs-1/sql-server-versions/2008-r2