List Database Properties

/* following adds procedure to a user database */
Use pubs
go
if exists ( select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_List_DataBase_Extended_Properties]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_List_DataBase_Extended_Properties]
GO


/* following creates procedure in a user database */
Create Procedure usp_List_DataBase_Extended_Properties
(
 @DatabaseName sysname, @type int
)
/*
author: Gary Andrews 8/7/2004
e-mail: garywandrews@cox.net
This procedure lists database properties.  It should work for SQL 7/2000.
When SQL 2005 comes out, you may have to mass change 'databaseproperty' to
'databasepropertyex'.  There is also a chance that SQL 2005 may have additional
properties that this script will not know about.

It has two parameters.
First parameter names the database whose properties are to be shown.
Second parameter limits properties shown to a specific type:
0 = show only properties which are currently set to false
1 = show only properties which are currently set to true
2 = show only properties which are currently set to null
3 = show only properties which are returning an error(s)
4 = show all properties
Version property is ALWAYS shown.
Miscellaneous- Invalid procedure parameters are not trapped
If all properties returned are NULL, you probably have not specified a valid DB name
*/
AS

Set nocount on
If @type > 4 or @type < 0
begin
set @type = 4
end

Create table #mytable(ServerName sysname, DatabaseName sysname, Properties sysname, Selection_Code smallint)

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

insert into #mytable values ( @TheServerName, @DatabaseName,

      CASE 
         WHEN databaseproperty(@DatabaseName,'IsAnsiNullDefault') IS NULL THEN 'IsAnsiNullDefault= Null'
         WHEN databaseproperty(@DatabaseName,'IsAnsiNullDefault') = 0 THEN 'IsAnsiNullDefault= False'
         WHEN databaseproperty(@DatabaseName,'IsAnsiNullDefault') = 1 THEN 'IsAnsiNullDefault= True'
         ELSE 'IsAnsiNullDefault=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsAnsiNullDefault') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsAnsiNullDefault') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsAnsiNullDefault') = 1 THEN 1
         ELSE 3
      end)


insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsAnsiNullsEnabled') IS NULL THEN 'IsAnsiNullsEnabled= Null'
         WHEN databaseproperty(@DatabaseName,'IsAnsiNullsEnabled') = 0 THEN 'IsAnsiNullsEnabled= False'
         WHEN databaseproperty(@DatabaseName,'IsAnsiNullsEnabled') = 1 THEN 'IsAnsiNullsEnabled= True'
         ELSE 'IsAnsiNullsEnabled=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsAnsiNullsEnabled') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsAnsiNullsEnabled') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsAnsiNullsEnabled') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsAnsiWarningsEnabled') IS NULL THEN 'IsAnsiWarningsEnabled= Null'
         WHEN databaseproperty(@DatabaseName,'IsAnsiWarningsEnabled') = 0 THEN 'IsAnsiWarningsEnabled= False'
         WHEN databaseproperty(@DatabaseName,'IsAnsiWarningsEnabled') = 1 THEN 'IsAnsiWarningsEnabled= True'
         ELSE 'IsAnsiWarningsEnabled=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsAnsiWarningsEnabled') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsAnsiWarningsEnabled') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsAnsiWarningsEnabled') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsAutoClose') IS NULL THEN 'IsAutoClose= Null'
         WHEN databaseproperty(@DatabaseName,'IsAutoClose') = 0 THEN 'IsAutoClose= False'
         WHEN databaseproperty(@DatabaseName,'IsAutoClose') = 1 THEN 'IsAutoClose= True'
         ELSE 'IsAutoClose=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsAutoClose') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsAutoClose') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsAutoClose') = 1 THEN 1
         ELSE 3
      end)


insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsAutoCreateStatistics') IS NULL THEN 'IsAutoCreateStatistics= Null'
         WHEN databaseproperty(@DatabaseName,'IsAutoCreateStatistics') = 0 THEN 'IsAutoCreateStatistics= False'
         WHEN databaseproperty(@DatabaseName,'IsAutoCreateStatistics') = 1 THEN 'IsAutoCreateStatistics= True'
         ELSE 'IsAutoCreateStatistics=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsAutoCreateStatistics') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsAutoCreateStatistics') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsAutoCreateStatistics') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsAutoShrink') IS NULL THEN 'IsAutoShrink= Null'
         WHEN databaseproperty(@DatabaseName,'IsAutoShrink') = 0 THEN 'IsAutoShrink= False'
         WHEN databaseproperty(@DatabaseName,'IsAutoShrink') = 1 THEN 'IsAutoShrink= True'
         ELSE 'IsAutoShrink=Error=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsAutoShrink') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsAutoShrink') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsAutoShrink') = 1 THEN 1
         ELSE 3
      end)


insert into #mytable values ( @TheServerName, @DatabaseName,     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsAutoUpdateStatistics') IS NULL THEN 'IsAutoUpdateStatistics= Null'
         WHEN databaseproperty(@DatabaseName,'IsAutoUpdateStatistics') = 0 THEN 'IsAutoUpdateStatistics= False'
         WHEN databaseproperty(@DatabaseName,'IsAutoUpdateStatistics') = 1 THEN 'IsAutoUpdateStatistics= True'
         ELSE 'IsAutoUpdateStatistics=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsAutoUpdateStatistics') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsAutoUpdateStatistics') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsAutoUpdateStatistics') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsBulkCopy') IS NULL THEN 'IsBulkCopy= Null'
         WHEN databaseproperty(@DatabaseName,'IsBulkCopy') = 0 THEN 'IsBulkCopy= False'
         WHEN databaseproperty(@DatabaseName,'IsBulkCopy') = 1 THEN 'IsBulkCopy= True'
         ELSE 'IsBulkCopy=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsBulkCopy') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsBulkCopy') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsBulkCopy') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsCloseCursorsOnCommitEnabled') IS NULL THEN 'IsCloseCursorsOnCommitEnabled= Null'
         WHEN databaseproperty(@DatabaseName,'IsCloseCursorsOnCommitEnabled') = 0 THEN 'IsCloseCursorsOnCommitEnabled= False'
         WHEN databaseproperty(@DatabaseName,'IsCloseCursorsOnCommitEnabled') = 1 THEN 'IsCloseCursorsOnCommitEnabled= True'
         ELSE 'IsCloseCursorsOnCommitEnabled=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsCloseCursorsOnCommitEnabled') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsCloseCursorsOnCommitEnabled') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsCloseCursorsOnCommitEnabled') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsDboOnly') IS NULL THEN 'IsDboOnly= Null'
         WHEN databaseproperty(@DatabaseName,'IsDboOnly') = 0 THEN 'IsDboOnly= False'
         WHEN databaseproperty(@DatabaseName,'IsDboOnly') = 1 THEN 'IsDboOnly= True'
         ELSE 'IsDboOnly=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsDboOnly') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsDboOnly') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsDboOnly') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsDetached') IS NULL THEN 'IsDetached= Null'
         WHEN databaseproperty(@DatabaseName,'IsDetached') = 0 THEN 'IsDetached= False'
         WHEN databaseproperty(@DatabaseName,'IsDetached') = 1 THEN 'IsDetached= True'
         ELSE 'IsDetached=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsDetached') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsDetached') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsDetached') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsEmergencyMode') IS NULL THEN 'IsEmergencyMode= Null'
         WHEN databaseproperty(@DatabaseName,'IsEmergencyMode') = 0 THEN 'IsEmergencyMode= False'
         WHEN databaseproperty(@DatabaseName,'IsEmergencyMode') = 1 THEN 'IsEmergencyMode= True'
         ELSE 'IsEmergencyMode=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsEmergencyMode') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsEmergencyMode') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsEmergencyMode') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsFulltextEnabled') IS NULL THEN 'IsFulltextEnabled= Null'
         WHEN databaseproperty(@DatabaseName,'IsFulltextEnabled') = 0 THEN 'IsFulltextEnabled= False'
         WHEN databaseproperty(@DatabaseName,'IsFulltextEnabled') = 1 THEN 'IsFulltextEnabled= True'
         ELSE 'IsFulltextEnabled=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsFulltextEnabled') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsFulltextEnabled') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsFulltextEnabled') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsInLoad') IS NULL THEN 'IsInLoad= Null'
         WHEN databaseproperty(@DatabaseName,'IsInLoad') = 0 THEN 'IsInLoad= False'
         WHEN databaseproperty(@DatabaseName,'IsInLoad') = 1 THEN 'IsInLoad= True'
         ELSE 'IsInLoad=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsInLoad') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsInLoad') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsInLoad') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsInRecovery') IS NULL THEN 'IsInRecovery= Null'
         WHEN databaseproperty(@DatabaseName,'IsInRecovery') = 0 THEN 'IsInRecovery= False'
         WHEN databaseproperty(@DatabaseName,'IsInRecovery') = 1 THEN 'IsInRecovery= True'
         ELSE 'IsInRecovery=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsInRecovery') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsInRecovery') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsInRecovery') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsInStandBy') IS NULL THEN 'IsInStandBy= Null'
         WHEN databaseproperty(@DatabaseName,'IsInStandBy') = 0 THEN 'IsInStandBy= False'
         WHEN databaseproperty(@DatabaseName,'IsInStandBy') = 1 THEN 'IsInStandBy= True'
         ELSE 'IsInStandBy=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsInStandBy') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsInStandBy') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsInStandBy') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsLocalCursorsDefault') IS NULL THEN 'IsLocalCursorsDefault= Null'
         WHEN databaseproperty(@DatabaseName,'IsLocalCursorsDefault') = 0 THEN 'IsLocalCursorsDefault= False'
         WHEN databaseproperty(@DatabaseName,'IsLocalCursorsDefault') = 1 THEN 'IsLocalCursorsDefault= True'
         ELSE 'IsLocalCursorsDefault=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsLocalCursorsDefault') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsLocalCursorsDefault') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsLocalCursorsDefault') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsNotRecovered') IS NULL THEN 'IsNotRecovered= Null'
         WHEN databaseproperty(@DatabaseName,'IsNotRecovered') = 0 THEN 'IsNotRecovered= False'
         WHEN databaseproperty(@DatabaseName,'IsNotRecovered') = 1 THEN 'IsNotRecovered= True'
         ELSE 'IsNotRecovered=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsNotRecovered') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsNotRecovered') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsNotRecovered') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsNullConcat') IS NULL THEN 'IsNullConcat= Null'
         WHEN databaseproperty(@DatabaseName,'IsNullConcat') = 0 THEN 'IsNullConcat= False'
         WHEN databaseproperty(@DatabaseName,'IsNullConcat') = 1 THEN 'IsNullConcat= True'
         ELSE 'IsNullConcat=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsNullConcat') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsNullConcat') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsNullConcat') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsOffline') IS NULL THEN 'IsOffline= Null'
         WHEN databaseproperty(@DatabaseName,'IsOffline') = 0 THEN 'IsOffline= False'
         WHEN databaseproperty(@DatabaseName,'IsOffline') = 1 THEN 'IsOffline= True'
         ELSE 'IsOffline=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsOffline') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsOffline') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsOffline') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsQuotedIdentifiersEnabled') IS NULL THEN 'IsQuotedIdentifiersEnabled= Null'
         WHEN databaseproperty(@DatabaseName,'IsQuotedIdentifiersEnabled') = 0 THEN 'IsQuotedIdentifiersEnabled= False'
         WHEN databaseproperty(@DatabaseName,'IsQuotedIdentifiersEnabled') = 1 THEN 'IsQuotedIdentifiersEnabled= True'
         ELSE 'IsQuotedIdentifiersEnabled=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsQuotedIdentifiersEnabled') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsQuotedIdentifiersEnabled') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsQuotedIdentifiersEnabled') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsReadOnly') IS NULL THEN 'IsReadOnly= Null'
         WHEN databaseproperty(@DatabaseName,'IsReadOnly') = 0 THEN 'IsReadOnly= False'
         WHEN databaseproperty(@DatabaseName,'IsReadOnly') = 1 THEN 'IsReadOnly= True'
         ELSE 'IsReadOnly=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsReadOnly') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsReadOnly') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsReadOnly') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsRecursiveTriggersEnabled') IS NULL THEN 'IsRecursiveTriggersEnabled= Null'
         WHEN databaseproperty(@DatabaseName,'IsRecursiveTriggersEnabled') = 0 THEN 'IsRecursiveTriggersEnabled= False'
         WHEN databaseproperty(@DatabaseName,'IsRecursiveTriggersEnabled') = 1 THEN 'IsRecursiveTriggersEnabled= True'
         ELSE 'IsRecursiveTriggersEnabled=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsRecursiveTriggersEnabled') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsRecursiveTriggersEnabled') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsRecursiveTriggersEnabled') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsShutDown') IS NULL THEN 'IsShutDown= Null'
         WHEN databaseproperty(@DatabaseName,'IsShutDown') = 0 THEN 'IsShutDown= False'
         WHEN databaseproperty(@DatabaseName,'IsShutDown') = 1 THEN 'IsShutDown= True'
         ELSE 'IsShutDown=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsShutDown') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsShutDown') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsShutDown') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsSingleUser') IS NULL THEN 'IsSingleUser= Null'
         WHEN databaseproperty(@DatabaseName,'IsSingleUser') = 0 THEN 'IsSingleUser= False'
         WHEN databaseproperty(@DatabaseName,'IsSingleUser') = 1 THEN 'IsSingleUser= True'
         ELSE 'IsSingleUser=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsSingleUser') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsSingleUser') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsSingleUser') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsSuspect') IS NULL THEN 'IsSuspect= Null'
         WHEN databaseproperty(@DatabaseName,'IsSuspect') = 0 THEN 'IsSuspect= False'
         WHEN databaseproperty(@DatabaseName,'IsSuspect') = 1 THEN 'IsSuspect= True'
         ELSE 'IsSuspect=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsSuspect') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsSuspect') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsSuspect') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsTruncLog') IS NULL THEN 'IsTruncLog= Null'
         WHEN databaseproperty(@DatabaseName,'IsTruncLog') = 0 THEN 'IsTruncLog= False'
         WHEN databaseproperty(@DatabaseName,'IsTruncLog') = 1 THEN 'IsTruncLog= True'
         ELSE 'IsTruncLog=Error'
      end,
      CASE 
         WHEN databaseproperty(@DatabaseName,'IsTruncLog') IS NULL THEN 2
         WHEN databaseproperty(@DatabaseName,'IsTruncLog') = 0 THEN 0
         WHEN databaseproperty(@DatabaseName,'IsTruncLog') = 1 THEN 1
         ELSE 3
      end)

insert into #mytable values ( @TheServerName, @DatabaseName,
     
      CASE 
         WHEN databaseproperty(@DatabaseName,'Version') IS NULL THEN 'Version= Database is closed'
         WHEN databaseproperty(@DatabaseName,'Version') = 0 THEN 'Version= False'
         WHEN databaseproperty(@DatabaseName,'Version') = 1 THEN 'Version= True'
         ELSE 'Version=' + cast((databaseproperty(@DatabaseName,'Version')) as varchar) + ' Database is open'
      end,
         5
      )


if @type = 0  
                begin              
/* false */select servername as 'Server Name/Instance', databasename as 'Database Name', properties as 'Properties' from #mytable where Selection_Code = 0 or Selection_Code = 5              
end
if @type = 1
/* true */select servername as 'Server Name/Instance', databasename as 'Database Name', properties as 'Properties' from #mytable where Selection_Code = 1 or Selection_Code = 5
if @type = 2
/* null */select servername as 'Server Name/Instance', databasename as 'Database Name', properties as 'Properties' from #mytable where Selection_Code = 2 or Selection_Code = 5
if @type = 3
/* errors */select servername as 'Server Name/Instance', databasename as 'Database Name', properties as 'Properties' from #mytable where Selection_Code = 3 or Selection_Code = 5
if @type = 4
/* all properties */select servername as 'Server Name/Instance', databasename as 'Database Name', properties as 'Properties' from #mytable


/* following line executes procedure to list database properties */
Exec usp_List_DataBase_Extended_Properties  'pubs', 4 -- list all properties