در پست قبلی در خصوص تابع SERVERPROPERTY صحبت شد که با استفاده از این تابع می توانستیم اطلاعاتی در خصوص همین Instance از SQL Server که در سیستم نصب شده در خروجی بدست آوریم.






در این پست، با استفاده از اسکریپت زیر که در SQL SERVER 2017 و 2019 قابل اجراست، کلیه Property هایی که مربوط به این Instance از اس کیو ال سرور است را در خروجی مشاهده خواهید کرد و نهایتا چها و چهار Property را در خروجی اسکریپت زیر دریافت خواهید کرد:

SELECT 'BuildClrVersion' AS Property, SERVERPROPERTY('BuildClrVersion') AS Value

UNION ALL

SELECT 'Collation', SERVERPROPERTY('Collation')

UNION ALL

SELECT 'CollationID', SERVERPROPERTY('CollationID')

UNION ALL

SELECT 'ComparisonStyle', SERVERPROPERTY('ComparisonStyle')

UNION ALL

SELECT 'ComputerNamePhysicalNetBIOS', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

UNION ALL

SELECT 'Edition', SERVERPROPERTY('Edition')

UNION ALL

SELECT 'EditionID', SERVERPROPERTY('EditionID')

UNION ALL

SELECT 'EngineEdition', SERVERPROPERTY('EngineEdition')

UNION ALL

SELECT 'HadrManagerStatus', SERVERPROPERTY('HadrManagerStatus')

UNION ALL

SELECT 'InstanceDefaultDataPath', SERVERPROPERTY('InstanceDefaultDataPath')

UNION ALL

SELECT 'InstanceDefaultLogPath', SERVERPROPERTY('InstanceDefaultLogPath')

UNION ALL

SELECT 'InstanceName', SERVERPROPERTY('InstanceName')

UNION ALL

SELECT 'IsAdvancedAnalyticsInstalled', SERVERPROPERTY('IsAdvancedAnalyticsInstalled')

UNION ALL

SELECT 'IsClustered', SERVERPROPERTY('IsClustered')

UNION ALL

SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')

UNION ALL

SELECT 'IsHadrEnabled', SERVERPROPERTY('IsHadrEnabled')

UNION ALL

SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')

UNION ALL

SELECT 'IsLocalDB', SERVERPROPERTY('IsLocalDB')

UNION ALL

SELECT 'IsPolyBaseInstalled', SERVERPROPERTY('IsPolyBaseInstalled')

UNION ALL

SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')

UNION ALL

SELECT 'IsXTPSupported', SERVERPROPERTY('IsXTPSupported')

UNION ALL

SELECT 'LCID', SERVERPROPERTY('LCID')

UNION ALL

SELECT 'LicenseType', SERVERPROPERTY('LicenseType')

UNION ALL

SELECT 'MachineName', SERVERPROPERTY('MachineName')

UNION ALL

SELECT 'NumLicenses', SERVERPROPERTY('NumLicenses')

UNION ALL

SELECT 'ProcessID', SERVERPROPERTY('ProcessID')

UNION ALL

SELECT 'ProductBuild', SERVERPROPERTY('ProductBuild')

UNION ALL

SELECT 'ProductBuildType', SERVERPROPERTY('ProductBuildType')

UNION ALL

SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')

UNION ALL

SELECT 'ProductMajorVersion', SERVERPROPERTY('ProductMajorVersion')

UNION ALL

SELECT 'ProductMinorVersion', SERVERPROPERTY('ProductMinorVersion')

UNION ALL

SELECT 'ProductUpdateLevel', SERVERPROPERTY('ProductUpdateLevel')

UNION ALL

SELECT 'ProductUpdateReference', SERVERPROPERTY('ProductUpdateReference')

UNION ALL

SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')

UNION ALL

SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')

UNION ALL

SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')

UNION ALL

SELECT 'ServerName', SERVERPROPERTY('ServerName')

UNION ALL

SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')

UNION ALL

SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')

UNION ALL

SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')

UNION ALL

SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')

UNION ALL

SELECT 'FilestreamShareName', SERVERPROPERTY('FilestreamShareName')

UNION ALL

SELECT 'FilestreamConfiguredLevel', SERVERPROPERTY('FilestreamConfiguredLevel')

UNION ALL

SELECT 'FilestreamEffectiveLevel', SERVERPROPERTY('FilestreamEffectiveLevel');

+------------------------------+------------------------------+

| Property                     | Value                        |

|------------------------------+------------------------------|

| BuildClrVersion              | v4.0.30319                   |

| Collation                    | SQL_Latin1_General_CP1_CI_AS |

| CollationID                  | 872468488                    |

| ComparisonStyle              | 196609                       |

| ComputerNamePhysicalNetBIOS  | sqlserver007                 |

| Edition                      | Developer Edition (64-bit)   |

| EditionID                    | -2117995310                  |

| EngineEdition                | 3                            |

| HadrManagerStatus            | 1                            |

| InstanceDefaultDataPath      | /var/opt/mssql/data/         |

| InstanceDefaultLogPath       | /var/opt/mssql/data/         |

| InstanceName                 | NULL                         |

| IsAdvancedAnalyticsInstalled | 1                            |

| IsClustered                  | 0                            |

| IsFullTextInstalled          | 0                            |

| IsHadrEnabled                | 0                            |

| IsIntegratedSecurityOnly     | 0                            |

| IsLocalDB                    | 0                            |

| IsPolyBaseInstalled          | 0                            |

| IsSingleUser                 | 0                            |

| IsXTPSupported               | 1                            |

| LCID                         | 1033                         |

| LicenseType                  | DISABLED                     |

| MachineName                  | sqlserver007                 |

| NumLicenses                  | NULL                         |

| ProcessID                    | 28                           |

| ProductBuild                 | 1800                         |

| ProductBuildType             | NULL                         |

| ProductLevel                 | CTP3.2                       |

| ProductMajorVersion          | 15                           |

| ProductMinorVersion          | 0                            |

| ProductUpdateLevel           | NULL                         |

| ProductUpdateReference       | NULL                         |

| ProductVersion               | 15.0.1800.32                 |

| ResourceLastUpdateDateTime   | 2019-07-17 22:15:31          |

| ResourceVersion              | 15.00.1800                   |

| ServerName                   | sqlserver007                 |

| SqlCharSet                   | 1                            |

| SqlCharSetName               | iso_1                        |

| SqlSortOrder                 | 52                           |

| SqlSortOrderName             | nocase_iso                   |

| FilestreamShareName          | MSSQLSERVER                  |

| FilestreamConfiguredLevel    | 0                            |

| FilestreamEffectiveLevel     | 0                            |

+------------------------------+------------------------------+

(44 rows affected)