SQL query on SharePoint content database: Step by Step 1

After a very long time we are back with fetching information directly from sharepoint content database with the help of SQL query.

check our previous post(s) here

But for this time we are going step by step.

First of all each and every web application has its own content database.


site collection(s)

To find the information about site collection there is a table called “Sites”

--This table content information of site collection(s)
select * from sites

--Number Site collection in a web application
select count(*) as 'Total Number OF Site Collection' from sites

Useful fields in sites table are ‘ID’ and ‘RootWebID’


Sites (a.k.a SPWeb)

To find the information about all the web in web application (that includes all the site collection) there is a table called “Web”

--This table content information of all the web
select * from webs

ID field of webs table is foreign key from rootwebid of sites table
So you can have relation like
--Relation of sites and web
Select * from webs inner join Sites on Webs.Id = Sites.RootWebId

Below are the useful fields.
Select
id,--unique ID
siteid,--site collection ID
parentwebid,--Null if top level site otherwise ID of webs
fullurl,--relative url
title,--web Title
description,--Web Description
masterurl,--Master page URL
custommasterurl,--Custom master page URL
sitelogourl,--site logo URL
sitelogodescription
from webs
inner join Sites on Webs.Id = Sites.RootWebId

This is the relation between web application, site collection, and then web from the database architectural point of view.

We will go further with list and libraries, and items but that will be step by step

Disclaimer

Running Direct queries on Sharepoint Database is not recommended by Microsoft.
Simply it's not for the faint of heart. Directly querying or modifying the database can place extra load on a server, or can expose information to users in a way that violates security policies or personal information management policies.We highly recommend doing all testing in a non-production environment and only for R & D purpose.