SQL筆記:查詢Microsoft SQL Server當前的版本
每次用到都要再查一次,所以將其記錄再此。這些指令可以查出你目前的Microsoft SQL Server的版本號,以及所依存的主機作業系統。
簡單查詢SQL版本
結果會以單一列資料的形式呈現。
Select @@version
-----
Microsoft SQL Server 2008 R2 (RTM) - 10.xx.xxxx (X64) May 31 2019 00:00:00 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1
詳細查詢SQL版本
如果要看比上面更詳細的資料(其實也沒多顯示很多東西,就大概是把欄位切一切分開顯示),可以考慮用下列的指令。
SELECT
@@SERVERNAME 'InstanceName',
CASE
WHEN CONVERT(VARCHAR(99), SERVERPROPERTY('ProductVersion')) like '8%' THEN 'SQL Server 2000'
WHEN CONVERT(VARCHAR(99), SERVERPROPERTY('ProductVersion')) like '9%' THEN 'SQL Server 2005'
WHEN CONVERT(VARCHAR(99), SERVERPROPERTY('ProductVersion')) like '10.0%' THEN 'SQL Server 2008'
WHEN CONVERT(VARCHAR(99), SERVERPROPERTY('ProductVersion')) like '10.5%' THEN 'SQL Server 2008R2'
WHEN CONVERT(VARCHAR(99), SERVERPROPERTY('ProductVersion')) like '11%' THEN 'SQL Server 2012'
WHEN CONVERT(VARCHAR(99), SERVERPROPERTY('ProductVersion')) like '12%' THEN 'SQL Server 2014'
WHEN CONVERT(VARCHAR(99), SERVERPROPERTY('ProductVersion')) like '13%' THEN 'SQL Server 2016'
WHEN CONVERT(VARCHAR(99), SERVERPROPERTY('ProductVersion')) like '14%' THEN 'SQL Server 2017'
ELSE 'unknown'
END AS 'SQLServerVersion',
CASE
WHEN SERVERPROPERTY('IsClustered') = 1 AND SERVERPROPERTY('IsHadrEnabled') = 1 THEN 'Failover Cluster + Availability Groups'
WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'Failover Cluster'
WHEN SERVERPROPERTY('IsHadrEnabled') = 1 THEN 'Availability Groups'
ELSE 'unknown'
END AS 'HighAvailability',
SERVERPROPERTY('ProductVersion') 'ProductVersion',
SERVERPROPERTY('ProductLevel') 'ProductLevel',
SERVERPROPERTY('Edition') 'ProductEdition',
SERVERPROPERTY('ProductUpdateLevel') 'ProductUpdateLevel',
SERVERPROPERTY('ProductBuildType') 'ProductBuildType',
SERVERPROPERTY('ProductUpdateReference') 'ProductUpdateReference',
DATABASEPROPERTYEX('master','Version') 'DatabaseInternalVersion'
GO