博客

在实际开发过程中，可能会遇到动态根据当前服务器版本版本不同来执行不同代码；

所以，需要一个函数可以检测当前数据库服务器的相关信息，具体代码如下：

/*******************************************
  文 件 名：UDF_GetServerInfo                                          
  创 建 人：jiazhitong          
  创建时间：2021/9/20            
  修 改 人：                                                                                
  修改时间：                                                                                
  修改原因：                                                                                
  功能描述：用户信息维护
  参    数：                                  
  返回值：
		
  处理流程：
 *******************************************/

CREATE FUNCTION UDF_GetServerInfo()
RETURNS @info TABLE (
	/*
		 Windows computer name on which the server instance is running.
		 For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.
		 NULL = Input is not valid, an error, or not applicable.
		 Base data type: nvarchar(128)
	 */
	MachineName NVARCHAR(128),
	/*
		Both the Windows server and instance information associated with a specified instance of SQL Server.
		NULL = Input is not valid, or an error.
		Base data type: nvarchar(128)
	 */
	ServerName  NVARCHAR(128),	
	/*
		Installed product edition of the instance of SQL Server. Use the value of this property to determine the features and the limits, 
		such as Compute Capacity Limits by Edition of SQL Server. 64-bit versions of the Database Engine append (64-bit) to the version.
		Returns:
		'Enterprise Edition'
		'Enterprise Edition: Core-based Licensing'
		'Enterprise Evaluation Edition'
		'Business Intelligence Edition'
		'Developer Edition'
		'Express Edition'
		'Express Edition with Advanced Services'
		'Standard Edition'
		'Web Edition'
		'SQL Azure' indicates SQL Database or Microsoft Azure Synapse Analytics
		'Azure SQL Edge Developer' indicates the development only edition for Azure SQL Edge
		'Azure SQL Edge' indicates the paid edition for Azure SQL Edge
		Base data type: nvarchar(128)
	*/
	Edition NVARCHAR(128),	
	/*
		Version of the instance of SQL Server, in the form of 'major.minor.build.revision'.
		Base data type: nvarchar(128)
	*/
	ProductVersion NVARCHAR(128), 
	ProductMajorVersion NVARCHAR(128),
	/*
		Level of the version of the instance of SQL Server.
		Returns one of the following:
		'RTM' = Original release version
		'SPn' = Service pack version
		'CTPn', = Community Technology Preview version
		Base data type: nvarchar(128)
	 */
	ProductLevel NVARCHAR(128),	
	/*
		Server is in single-user mode.
		1 = Single user.
		0 = Not single user
		NULL = Input is not valid, an error, or not applicable.
		Base data type: int
	 */
	IsSingleUser INT,
	/*
		Server is in integrated security mode.
		1 = Integrated security (Windows Authentication)
		0 = Not integrated security. (Both Windows Authentication and SQL Server Authentication.)
		NULL = Input is not valid, an error, or not applicable.
		Base data type: int
	 */
	IsIntegratedSecurityOnly INT,
	/*
		The full-text and semantic indexing components are installed on the current instance of SQL Server.
		1 = Full-text and semantic indexing components are installed.
		0 = Full-text and semantic indexing components are not installed.
		NULL = Input is not valid, an error, or not applicable.
		Base data type: int
	 */
	IsFullTextInstalled INT,
	/*
		Name of the instance to which the user is connected.
		Returns NULL if the instance name is the default instance, if the input is not valid, or error.
		NULL = Input is not valid, an error, or not applicable.
		Base data type: nvarchar(128)
	 */
	InstanceName NVARCHAR(128),
	/*
		Database Engine edition of the instance of SQL Server installed on the server.
		1 = Personal or Desktop Engine (Not available in SQL Server 2005 (9.x) and later versions.)
		2 = Standard (This is returned for Standard, Web, and Business Intelligence.)
		3 = Enterprise (This is returned for Evaluation, Developer, and Enterprise editions.)
		4 = Express (This is returned for Express, Express with Tools, and Express with Advanced Services)
		5 = SQL Database
		6 = Microsoft Azure Synapse Analytics
		8 = Azure SQL Managed Instance
		9 = Azure SQL Edge (This is returned for all editions of Azure SQL Edge)
		11 = Azure Synapse serverless SQL pool
		Base data type: int
	 */
	EngineEdition INT,
	/*
		Name of the default collation for the server.
		NULL = Input is not valid, or an error.
		Base data type: nvarchar(128)
	 */
	Collation NVARCHAR(128)
)
AS
BEGIN
	INSERT INTO @info
	(
		MachineName,
		ServerName,
		Edition,
		ProductVersion,
		ProductLevel,
		IsSingleUser,
		IsIntegratedSecurityOnly,
		IsFullTextInstalled,
		InstanceName,
		EngineEdition,
		Collation
	)	
	SELECT  
		CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(128)) AS ComputerName
		,CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128)) AS ServerName  
		,CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128)) AS Edition  
		,CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)) AS ProductVersion  
		,CAST(SERVERPROPERTY('ProductLevel') AS NVARCHAR(128)) AS ProductLevel
		,CAST(SERVERPROPERTY('IsSingleUser') AS INT) AS IsSingleUser  
		,CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS INT) AS IsIntegratedSecurityOnly
		,CAST(SERVERPROPERTY('IsFullTextInstalled') AS INT ) AS IsFullTextInstalled
		,CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(128)) AS InstanceName
		,CAST(SERVERPROPERTY('EngineEdition') AS INT) AS EngineEdition
		,CAST(SERVERPROPERTY('Collation') AS NVARCHAR(128)) AS Collation
	
	--	
	UPDATE @info
	SET
		ProductMajorVersion = CASE WHEN CHARINDEX('.',i.ProductVersion)>0 THEN 
									SUBSTRING(i.ProductVersion,1,CHARINDEX('.',i.ProductVersion)-1) 
							  ELSE 
							  		NULL 
		                      END 
	FROM @info AS i
	
	--
	RETURN    
END