'SQL SERVER' 카테고리의 다른 글
| Procedure 실행횟수 확인 (0) | 2018.10.27 |
|---|---|
| 실행중인 쿼리 확인 (0) | 2018.10.27 |
| Index 생성이 안되어 있는 Foreign key 찾기 (0) | 2018.10.27 |
| 링크드 서버(linked Server) (0) | 2018.10.26 |
| Linked Server 연결 테스트(TEST) (0) | 2018.10.26 |
2018. 10. 27. 22:33
SQL SERVER
alter proc sp_list_server_property
AS
DECLARE @props TABLE ( propertyname sysname PRIMARY KEY)
INSERT INTO @props( propertyname )
SELECT 'BuildClrVersion'
UNION
SELECT 'Collation'
UNION
SELECT 'CollationID'
UNION
SELECT 'ComparisonStyle'
UNION
SELECT 'ComputerNamePhysicalNetBIOS'
UNION
SELECT 'Edition'
UNION
SELECT 'HadrManagerStatus'
UNION
SELECT 'EngineEdition'
UNION
SELECT 'InstanceName'
UNION
SELECT 'IsClustered'
UNION
SELECT 'IsFullTextInstalled'
UNION
SELECT 'IsIntegratedSecurityOnly'
UNION
SELECT 'IsSingleUser'
UNION
SELECT 'LCID'
UNION
SELECT 'LicenseType'
UNION
SELECT 'MachineName'
UNION
SELECT 'NumLicenses'
UNION
SELECT 'ProcessID'
UNION
SELECT 'ProductVersion'
UNION
SELECT 'ProductLevel'
UNION
SELECT 'ResourceLastUpdateDateTime'
UNION
SELECT 'ResourceVersion'
UNION
SELECT 'ServerName'
UNION
SELECT 'SqlCharSet'
UNION
SELECT 'SqlCharSetName'
UNION
SELECT 'SqlSortOrder'
UNION
SELECT 'SqlSortOrderName'
UNION
SELECT 'FilestreamShareName'
UNION
SELECT 'FilestreamConfiguredLevel'
UNION
SELECT 'FilestreamEffectiveLevel'
SELECT propertyname , SERVERPROPERTY ( propertyname ) FROM @props
UNION ALL
SELECT 'EditionID' AS propertyname,
CASE SERVERPROPERTY ( 'EditionID' )
WHEN 1804890536 THEN 'Enterprise'
WHEN 1872460670 THEN 'Enterprise With CORE Base Liserence'
WHEN 610778273 THEN 'Enterprise Evaluation'
WHEN 284895786 THEN 'Business Intelligence'
WHEN - 2117995310 THEN 'Developer'
WHEN - 1592396055 THEN 'Express'
WHEN - 133711905 THEN 'Express with Advanced Services'
WHEN - 1534726760 THEN 'Standard'
WHEN 1293598313 THEN 'WEB'
END AS EditionId
UNION ALL
SELECT 'IsHadrEnabled' AS propertyname,
CASE SERVERPROPERTY ( 'IsHadrEnabled' )
WHEN 0 THEN 'AlwaysOn 가용성 그룹 기능을 사용하지 않습니다.'
WHEN 1 THEN 'AlwaysOn 가용성 그룹 기능을 사용합니다'
END AS IsHadrEnabled
UNION ALL
SELECT 'HadrManagerStatus' AS propertyname,
CASE SERVERPROPERTY ( 'HadrManagerStatus' )
WHEN 0 THEN '시작되지 않았습니다. 통신 보류 중입니다.'
WHEN 1 THEN '시작되어 실행 중입니다.'
WHEN 2 THEN '시작되지 않고 실패했습니다.'
END AS HadrManagerStatus