List Server Extended Properties

/* following drops procedure from master database */
Use master
go
if exists ( select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_List_Server_Extended_Properties]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_List_Server_Extended_Properties]
GO


/* following creates procedure in a master database */
Create Procedure dbo.usp_List_Server_Extended_Properties

/*
author: Gary Andrews 8/8/2004
e-mail: garywandrews@cox.net
This procedure lists server properties.  It should work for SQL 7/2000.
When SQL 2005 comes out, you may have to mass change 'serverproperty' to
'serverpropertyex'.  There is also a chance that SQL 2005 may have additional
properties that this script will not know about.
*/
AS

Set nocount on

Create table #mytable(ServerName sysname, Properties nvarchar(256))

Declare @TheServerName sysname
set @TheServerName = cast(SERVERPROPERTY ( 'ServerName' ) as sysname)

insert into #mytable values ( @TheServerName, 
        case
         when serverproperty('Collation') is NULL Then 'Collation default is unknown'
        else
'Collation default for the server is ' + cast(SERVERPROPERTY ( 'Collation' ) as nvarchar)
        end)

insert into #mytable values ( @TheServerName, 
        case
         when serverproperty('Edition') is NULL Then 'Edition is unknown'
        else
'Edition is ' + cast(SERVERPROPERTY ( 'Edition' ) as sysname)
        end)     

insert into #mytable values ( @TheServerName, 
CASE 
         when serverproperty('Engine Edition') is NULL Then 'Engine Edition is unknown'
         WHEN serverproperty('Engine Edition') = 1 THEN 'Engine Edition is Personal or Desktop Engine (MSDE 2000)'
         WHEN serverproperty('Engine Edition') = 2 THEN 'Engine Edition is Standard'
 When serverproperty('Engine Edition') = 3 then 'Engine Edition is Enterprise, Enterprise Evaluation, or Developer'
         ELSE 'Engine Edition is unknown'
      end)

insert into #mytable values ( @TheServerName, 
case
 when serverproperty('InstanceName') is NULL Then 'Instance name is not applicable (this is default instance)'
         else 'Instance name is ' + cast(SERVERPROPERTY ( 'InstanceName' ) as nvarchar)
        end)     


insert into #mytable values ( @TheServerName, 
case
 when serverproperty('IsClustered') is NULL Then 'Is Clustered status is unknown'
         WHEN serverproperty('IsClustered') = 0 THEN 'Server is not clustered'
         WHEN serverproperty('IsClustered') = 1 THEN 'Server is clustered'
         ELSE 'Is Clustered status is unknown'
         end)    


insert into #mytable values ( @TheServerName, 
case
 when serverproperty('IsFullTextInstalled') is NULL Then 'Is Full Text Installed status is unknown'
         WHEN serverproperty('IsFullTextInstalled') = 0 THEN 'Full Text is not installed'
         WHEN serverproperty('IsFullTextInstalled') = 1 THEN 'Full Text is installed'
         else
          'Is Full Text Installed status is unknown'
         end)    

insert into #mytable values ( @TheServerName, 
case
 when serverproperty('IsIntegratedSecurityOnly') is NULL Then 'Is Integrated Security Only status is unknown'
         WHEN serverproperty('IsIntegratedSecurityOnly') = 0 THEN 'Is not in Integrated Security Only mode'
         WHEN serverproperty('IsIntegratedSecurityOnly') = 1 THEN 'Is in Integrated Security Only mode'
         else
          'Is Integrated Security Only status is unknown'
         end)   

insert into #mytable values ( @TheServerName, 
case
 when serverproperty('IsSingleUser') is NULL Then 'Is in Single User mode status is unknown'
         WHEN serverproperty('IsSingleUser') = 0 THEN 'Is not in Single User mode'
         WHEN serverproperty('IsSingleUser') = 1 THEN 'Is in Single User mode'
         else
          'Is in Single User mode status is unknown'
         end)  

insert into #mytable values ( @TheServerName, 
case
         WHEN serverproperty('IsSyncWithBackup') is NULL THEN 'IsSyncWithBackup status is unknown'
 WHEN serverproperty('IsSyncWithBackup') = 0 THEN 'IsSyncWithBackup status is false'
         WHEN serverproperty('IsSyncWithBackup') = 1 THEN 'IsSyncWithBackup status is true'
          else
          'IsSyncWithBackup status is unknown'
         end)  

insert into #mytable values ( @TheServerName, 
case
         WHEN serverproperty('LicenseType') is NULL THEN 'License type is unknown.'
 else
'License Type is ' + cast(SERVERPROPERTY ( 'LicenseType' ) as nvarchar(256))
         end)  

insert into #mytable values ( @TheServerName, 
case
         WHEN serverproperty('MachineName') is NULL THEN 'Machine Name is unknown.'
 else
'Machine Name is ' + cast(SERVERPROPERTY ( 'MachineName' ) as nvarchar(256))
         end)  

insert into #mytable values ( @TheServerName, 
case
         WHEN serverproperty('NumLicenses') is NULL THEN 'Number of Licenses is unknown'
 else
'Number of Licenses is ' + cast(SERVERPROPERTY ( 'NumLicenses' ) as nvarchar(256))
         end)  

insert into #mytable values ( @TheServerName, 
case
         WHEN serverproperty('ProcessID') is NULL THEN 'ProcessID is unknown'
 else
'ProcessID is ' + cast(SERVERPROPERTY ( 'ProcessID' ) as nvarchar(256))
         end) 

insert into #mytable values ( @TheServerName, 
        case
         when serverproperty('ProductVersion') is NULL THEN 'Product Version is unknown'
         else
'ProductVersion (major.minor.build) is ' + cast(SERVERPROPERTY ( 'ProductVersion' ) as nvarchar(256))
         end)  

insert into #mytable values ( @TheServerName, 
        case
         when serverproperty('ProductLevel') is null then 'Product Level is unknown'
 else
'ProductLevel is ' + cast(SERVERPROPERTY ( 'ProductLevel' ) as nvarchar(256))
         end)  

Select servername as 'Server Name', properties as 'Properties' from #mytable               


/* following line executes procedure to list server properties */
Exec master.dbo.usp_List_Server_Extended_Properties   -- list all properties