It has been a couple of years now that I have been working in SharePoint environment with different levels of complexities. Recently we have added an Always On cluster node, for which retriving information like the name of a content database for a particular site collection is not simple anymore. I will talk about a couple of important tables in the SharePoint configuration database related to a SharePoint application that help us to identify the databases and the name of the database servers.

The SharePoint configuration database has a number of important tables. These tables have a lot of information that can be very crucial in understanding the links between internals of SharePoint.  I will talk about Objects, Class and the SiteMap tables in SharePoint configuration database. I definitely do miss the documentation on these tables. However, there are a couple of blogs that speak about some old versions of the conguration, which helped me to understand the internals. 

There are instances when my application team comes up to me and asks what databases are mounted and being used by the application. I have created a script joining the tables in SharePoint configuration database, as is mentioned below.

• The sitemap table has a lot of metadata information required for SharePoint to maintain a SiteCollection. Some of the columns, such as applicationId, databaseId, status, version, hostheaderissitename, subscriptionId, etc., are used to prepare the last script (see the final image below).
• The objects table will have the entries for all of the objects that are related to SharePoint applications like the server name, database, etc. This table is a denormalised table and has one row each for each object. We need to self join this table to identify the metadata information.
• The Classes table has the id, baseclassid and fullName columns, which are useful to get basic information.

A basic query to give us the sitemapid, applicationid and databaseid and the sitecollection url is shown here.

The sitemapid mentioned here is the unique id for each and every sitecollection in the sitemap table of the SharePoint configuration database. The databaseid shown here is the id for the content database, which hosts the sitecollection.

To get the name of the database I need to join the sitemap and objects table, as shown here.

I have joined the sitemap and the objects table on the databaseid and the id, respectively. As mentioned earlier, the objects table is a denormalised table and has an entry for each and every object, be it a database, application, SharePoint list, SharePoint document etc. 

Let us move to the next section of this post where I will discuss other details about the hierarchical nature of the objects table to get the server name of the databases mounted in the SharePoint application.

Here I have self joined the objects table thrice to get the information of the server name from the name of the database. There are levels of hierarchy in the objects table which we need to realise to understand the query. First, I identified the database name from the sitemapid table and objects table join. Then I identified the parentid of the database object id. In the last join I found the server name of the database by identifying the next hirerarchial level for the parent id identified in the previous step. So, the servername is 2 levels deeper in the objects table. 

Along the same lines, we can use the sitemap, objects, and other metadata tables in the SharePoint configuration database to get the information we may require. This might be information like orphan sites or the databases that are mounted. I hope this will give some perspective to the community to figure out more information.

Thanks,

Tushar Kanti