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?
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.
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:
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 (
insert into #properties
exec sp_MsForeachDb '
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 database_name, name=convert(varchar(max),name), value=convert(varchar(max),value)
) AS SourceTable
FOR name IN ([Database Contact], [Maintenance Window])
) AS PivotTable;
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.
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