Thursday, August 27, 2009

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.

No comments:




Share your SharePoint Experiences with us...
As good as the SharePointKings is, we want to make it even better. One of our most valuable sources of input for our Blog Posts comes from ever enthusiastic Visitors/Readers. We welcome every Visitor/Reader to contribute their experiences with SharePoint. It may be in the form of a code stub, snippet, any tips and trick or any crazy thing you have tried with SharePoint.
Send your Articles to sharepointkings@gmail.com with your Profile Summary. We will Post them. The idea is to act as a bridge between you Readers!!!

If anyone would like to have their advertisement posted on this blog, please send us the requirement details to sharepointkings@gmail.com