Use Extended Properties to store information about a database

A lot of our clients have a large number of third-party databases and applications that they host themselves. This creates an obvious challenge of knowing and understanding what is in each database, what application connects to, vendor details and so on. Usually, this information is stored in a service catalogue or a simple spreadsheet.

As DBAs, we like to have this information in the database so it is easily accessible whilst working on the server, without maintaining or having to open any other documents. However, adding anything to vendor-provided databases is not recommended as it could interfere with the application or void the warranty and support. So how do we do it?

Extended Properties

A handy trick is to use SQL Server Extended Properties to store additional information about SQL Server objects, including databases. Once added, this information is readily available in SQL Server Management Studio and accessible programmatically via T-SQL. Extended Properties are not a new feature and have been around for nearly two decades.

Add Property

We can add Extended Properties for any database in the Management Studio. To do so, right-click the database and select Properties; then, in Extended Properties, add the values you want:

We can also add extended properties via T-SQL:

EXEC sys.sp_addextendedproperty @name=N'Database Contact', @value=N'[email protected]' 

Update Property

To update the property in the Management Studio, we can type in the new information and press OK. It is no different to updating a cell in a spreadsheet.

To update programmatically using T-SQL, we can use the following:

EXEC sys.sp_updateextendedproperty @name=N'Database Contact', @value=N'[email protected]' 

Delete Property

Deleting property in the Management Studio is as easy as clicking on the property and then clicking the Delete button. Deleting programmatically in T-SQL is similar to adding or updating properties:

EXEC sys.sp_dropextendedproperty @name=N'Database Contact' 

Read Property

Reading properties in the Management Studio is self-explanatory, and we can read properties via T-SQL using the fn_listextendedproperty function:

SELECT database_name = DB_NAME(), name, value  
FROM fn_listextendedproperty(default, default, default, default, default, default, default);  

The real benefit

As you may already suspect, the real benefit of using Extended Properties is the ability to use T-SQL to query multiple databases and even servers at once. This way, we can easily create a simple, self-documenting service catalogue without maintaining spreadsheets:

drop table if exists #properties;

create table #properties (
	database_name nvarchar(255),
	name sql_variant,
	value sql_variant
)

insert into #properties
exec sp_MsForeachDb '
	USE [?]
	SELECT databaese_name = DB_NAME(), l.name, l.value
	from sys.databases d
	outer apply fn_listextendedproperty(default, default, default, default, default, default, default) l
	where d.name = DB_NAME()
	and database_id > 4
'

select * from #properties

And we can do the same against multiple servers using the built-in SQL Server Central Management Server:

Let’s say we have more than one property for each database and want to make it look like a spreadsheet. We can use the PIVOT function in SQL Server:

SELECT *
FROM  
(
	select database_name, name=convert(varchar(max),name), value=convert(varchar(max),value) 
	from #properties
) AS SourceTable  
PIVOT  
(  
  max(value)
  FOR name IN ([Database Contact], [Maintenance Window])  
) AS PivotTable;  

Central repository

We could take this further and have all this automatically stored in a central “DBADMIN” database and synchronised with individual databases. This way, we’d never lose any information, even if the vendor deletes our properties (yes, some vendors do all sorts of ugly stuff), and if they did, we would push the property back to the database in question.

Conclusion

Querying SQL Server allows us to see all databases simultaneously without updating spreadsheets or cross-matching with spreadsheets. As we see in the example above, we have two databases with no ‘Database Owner’ across two servers that we can quickly rectify without having to wonder if we have all this info in a spreadsheet, and you combine this with our Production Readiness Guide