T-SQLでサーバプロパティを表示する方法

SQL Serverでは、サーバープロパティをGUIから確認できますが、他のドキュメントなどへ貼り付けて利用する場合は手間になります。

ですが、T-SQLのクエリを利用することで、サーバープロパティをコピー&ペーストすることができる形で出力する事が出来ます。

全てのサーバープロパティを表示できるSQL一覧

Microsoft SQL Server Management Studioから下記クエリを実行することでサーバープロパティが全て表示されます。

SELECT
 SERVERPROPERTY ( 'BuildClrVersion' ) AS BuildClrVersion,
 SERVERPROPERTY ( 'Collation' ) AS Collation,
 SERVERPROPERTY ( 'CollationID' ) AS CollationID,
 SERVERPROPERTY ( 'ComparisonStyle' ) AS ComparisonStyle,
 SERVERPROPERTY ( 'ComputerNamePhysicalNetBIOS' ) AS ComputerNamePhysicalNetBIOS,
 SERVERPROPERTY ( 'Edition' ) AS Edition,
 SERVERPROPERTY ( 'EditionID' ) AS EditionID,
 SERVERPROPERTY ( 'EngineEdition' ) AS EngineEdition,
 SERVERPROPERTY ( 'FilestreamConfiguredLevel' ) AS FilestreamConfiguredLevel,
 SERVERPROPERTY ( 'FilestreamEffectiveLevel' ) AS FilestreamEffectiveLevel,
 SERVERPROPERTY ( 'FilestreamShareName' ) AS FilestreamShareName,
 SERVERPROPERTY ( 'HadrManagerStatus' ) AS HadrManagerStatus,
 SERVERPROPERTY ( 'InstanceDefaultBackupPath' ) AS InstanceDefaultBackupPath,
 SERVERPROPERTY ( 'InstanceDefaultDataPath' ) AS InstanceDefaultDataPath,
 SERVERPROPERTY ( 'InstanceDefaultLogPath' ) AS InstanceDefaultLogPath,
 SERVERPROPERTY ( 'InstanceName' ) AS InstanceName,
 SERVERPROPERTY ( 'IsAdvancedAnalyticsInstalled' ) AS IsAdvancedAnalyticsInstalled,
 SERVERPROPERTY ( 'IsBigDataCluster' ) AS IsBigDataCluster,
 SERVERPROPERTY ( 'IsClustered' ) AS IsClustered,
 SERVERPROPERTY ( 'IsExternalAuthenticationOnly' ) AS IsExternalAuthenticationOnly,
 SERVERPROPERTY ( 'IsExternalGovernanceEnabled' ) AS IsExternalGovernanceEnabled,
 SERVERPROPERTY ( 'IsFullTextInstalled' ) AS IsFullTextInstalled,
 SERVERPROPERTY ( 'IsHadrEnabled' ) AS IsHadrEnabled,
 SERVERPROPERTY ( 'IsIntegratedSecurityOnly' ) AS IsIntegratedSecurityOnly,
 SERVERPROPERTY ( 'IsLocalDB' ) AS IsLocalDB,
 SERVERPROPERTY ( 'IsPolyBaseInstalled' ) AS IsPolyBaseInstalled,
 SERVERPROPERTY ( 'IsServerSuspendedForSnapshotBackup' ) AS IsServerSuspendedForSnapshotBackup,
 SERVERPROPERTY ( 'IsSingleUser' ) AS IsSingleUser,
 SERVERPROPERTY ( 'IsTempDbMetadataMemoryOptimized' ) AS IsTempDbMetadataMemoryOptimized,
 SERVERPROPERTY ( 'IsXTPSupported' ) AS IsXTPSupported,
 SERVERPROPERTY ( 'LCID' ) AS LCID,
 SERVERPROPERTY ( 'LicenseType' ) AS LicenseType,
 SERVERPROPERTY ( 'MachineName' ) AS MachineName,
 SERVERPROPERTY ( 'NumLicenses' ) AS NumLicenses,
 SERVERPROPERTY ( 'PathSeparator' ) AS PathSeparator,
 SERVERPROPERTY ( 'ProcessID' ) AS ProcessID,
 SERVERPROPERTY ( 'ProductBuild' ) AS ProductBuild,
 SERVERPROPERTY ( 'ProductBuildType' ) AS ProductBuildType,
 SERVERPROPERTY ( 'ProductLevel' ) AS ProductLevel,
 SERVERPROPERTY ( 'ProductMajorVersion' ) AS ProductMajorVersion,
 SERVERPROPERTY ( 'ProductMinorVersion' ) AS ProductMinorVersion,
 SERVERPROPERTY ( 'ProductUpdateLevel' ) AS ProductUpdateLevel,
 SERVERPROPERTY ( 'ProductUpdateReference' ) AS ProductUpdateReference,
 SERVERPROPERTY ( 'ProductVersion' ) AS ProductVersion,
 SERVERPROPERTY ( 'ResourceLastUpdateDateTime' ) AS ResourceLastUpdateDateTime,
 SERVERPROPERTY ( 'ResourceVersion' ) AS ResourceVersion,
 SERVERPROPERTY ( 'ServerName' ) AS ServerName,
 SERVERPROPERTY ( 'SqlCharSet' ) AS SqlCharSet,
 SERVERPROPERTY ( 'SqlCharSetName' ) AS SqlCharSetName,
 SERVERPROPERTY ( 'SqlSortOrder' ) AS SqlSortOrder,
 SERVERPROPERTY ( 'SqlSortOrderName' ) AS SqlSortOrderName,
 SERVERPROPERTY ( 'SuspendedDatabaseCount' ) AS SuspendedDatabaseCount;
GO