Identify the Database Server which is used to mount the database in Sharepoint 2010

Hi Guys,
Presently working in 2nd largest Sharepoint Environment globally. I have been working with a lot of Sharepoint Enironments with different levels of complexities. Well Today I will talk about the a couple of important tables in the Config database related to Sharepoint.
As you know the config database houses a lot of important tables. These tables have a lot of information which can be very crucial in understanding the links between a lot of internals inside sharepoint. Today we will take a peek into the Objects, Class and the SiteMap tables in the COnfig database. I definitely do miss the documentation on these tables. However there were little bread crums over the internet which I could follow and reach to this juncture.
The article is divided into two parts in the first part we will look  into the very basics of queries and in the next section we will look into the complex query merging a couple of the basic queries.

PART 1

a.SiteMap Table will have most of the information required for the Sharepoint to maintain a SiteCollection. ApplicationId,DatabaseId,Status,Version,HostHeaderIsSiteName, SubscriptionId,etc colums are there which are very useful in SiteMap table.

b.Objects Table will have the entries for most the objects that are related to config may be the Server Names,Databases,etc information.  Id,ClassId, ParentId , Name ,Status ,Version etc are colmns which are very useful in Objects table.

c.CLasses Table will have the Id,BaseClassId and FullName columns which will be useful to get basic information.

To Identify The Databases mounted in the Farm:

Query1 -> Select distinct DatabaseID from SiteMap

This Query1 will give us the databaseid's mounted for the whole farm sharing the config database.

Query2 -> Select distinct Name as DBName , DatabaseId,ParentId

from SiteMap s join Objects o on s.DatabaseID=o.ID

This Query2 will give us  the database names for the Farm that are presently mounted.

These two queries are good to start with the inquition of database information inside Sharepoint Config database.

PART2

Now lets move to the next section of this post where we will discuss other details about the database servers that are being used in Sharepoint to Mount the databases.

Query 3 -> select distinct O.Name As DatabaseName, servers.Name as ServerName

from SiteMap s

inner join Objects o on o.id= s.databaseid

inner join Objects Parent on Parent.Id= o.ParentId

inner join Objects servers on servers.Id= Parent.ParentId

This query will hunt for the parentid to which the database is belonging and then hunt for the child for the Servers and joining them we identify the Databse Servers for our Databases. Here we are self joining the Objects table to get all the information.

Thanks,

Tushar Kanti