【IT168技术文档】

　　除了使用企业管理器，我们还可以使用如下函数：SERVERPROPERTY ( propertyname )，DATABASEPROPERTYEX( database , property )

　　查询有关服务器实例的属性信息的一组SQL 如下：

SET NOCOUNT ON
select SERVERPROPERTY('Collation') as 'Collation 服务器的默认排序规则名称'
select SERVERPROPERTY('Edition') as 'Edition 实例版本'
select case SERVERPROPERTY('Engine Edition') when 1 then '个人或 Desktop Engine' 
when 2 then '标准' when 3 then '企业（适用于企业版、企业评估版和开发版）' 
else SERVERPROPERTY('Engine Edition') end as 'Engine Edition 实例引擎版本'
select SERVERPROPERTY('InstanceName') as 'InstanceName 实例名称'
select case SERVERPROPERTY('IsClustered') when 1 then '聚集' 
when 0 then '非聚集' when NULL then '输入无效或发生错误' 
else SERVERPROPERTY('IsClustered') end as 'IsClustered 在故障转移群集中配置服务器实例'
select case SERVERPROPERTY('IsFullTextInstalled') 
when 1 then '已安装全文组件' when 0 then '未安装全文组件' when NULL then '输入无效或发生错误' 
else SERVERPROPERTY('IsFullTextInstalled') end as 'IsFullTextInstalled 安装全文组件'
select case SERVERPROPERTY('IsIntegratedSecurityOnly') when 1 then '集成安全模式' 
when 0 then '非集成安全模式' when NULL then '输入无效或发生错误' 
else SERVERPROPERTY('IsIntegratedSecurityOnly') end as 'IsIntegratedSecurityOnly 集成安全模式'
select case SERVERPROPERTY('IsSingleUser') 
when 1 then '单用户' when 0 then '非单用户' when NULL then '输入无效或发生错误'
else SERVERPROPERTY('IsSingleUser') end as 'IsSingleUser 单用户模式'
select case SERVERPROPERTY('IsSyncWithBackup') 
when 1 then '真' when 0 then '假' else SERVERPROPERTY('IsSyncWithBackup') 
end as 'IsSyncWithBackup 发布数据库或分发数据库'
select case SERVERPROPERTY('LicenseType') when 'PER_SEAT' then '单机模式' 
when 'PER_PROCESSOR' then '单处理器模式' when 'DISABLED' then '禁用许可' 
else SERVERPROPERTY('LicenseType') end as 'LicenseType 实例许可模式'
select SERVERPROPERTY('MachineName') as 'Windows NT 计算机名称'
select SERVERPROPERTY('NumLicenses') as 'NumLicenses 许可数'
select SERVERPROPERTY('ProcessID') as 'SQL Server 服务的进程 ID'
select SERVERPROPERTY('ProductVersion') as 'ProductVersion 实例的版本'
select case SERVERPROPERTY('ProductLevel') when 'RTM' then '发售版' 
when 'SPn' then '服务包版' when 'Bn' then 'beta 测试版' 
else SERVERPROPERTY('ProductLevel') end as 'ProductLevel 版本级别'
select SERVERPROPERTY('ServerName') as '实ServerName 例信息'
GO

　　查询有关数据库属性信息示例：

SELECT DATABASEPROPERTYEX( 'plm_30' , 'Collation' )

　　修改数据库的collate：

ALTER DATABASE database_name collate = 'Chinese_PRC_CI_AS'