How to find page/site customization status – Part 1 – with the help of Content Database Schema

Till now, that was one of my toughest assignment, my requirement was to find all pages which customized or changed from SharePoint designer, visual studio and any editor tool, when I started working on the requirements, I thought that I can easily find page customized status (ghosted/unghosted) from SharePoint object model - page CustomizedPageStatus enumeration, but that flag gave me wrong value, I mean, I can’t see my pages as customized which I created from SharePoint Designer.

 

At one time, I don’t know what to searched in Google, because I tried all combination of search query to get some useful output, but It didn’t bring me anything, all search result gave me idea and links about ghosting, how to customize page and how to reset to site definition from SharePoint designer, etc…..

 

After some time, I have found one link which says how to deal and work with SharePoint content database, so I started playing around with content database and I have found out that I can write a query which will give me page customization status, SharePoint stores all the information into database, “ALLDOCS” table has all information regarding page size, version, attached list, modified user, time and many more ….

 

By looking into schema for ALLDOCS table, I created database query and finally I got my result and I was so happy to see the result :)

 

Below is sample query which will give us all the pages which customized/created from SharePoint Designer, Visual Studio or any other editor tool.

 

QUERY:

 

SELECT

W.Title, W.FullURL,

D.DirName + '/' +  D.LeafName as pageName,

TimeLastModified as ModifiedTime,

*

FROM

Alldocs  D With (NoLock)

JOIN Webs W With (NoLock)

On W.ID=D.WebID

WHERE

D.hasstream = '1'

and D.extension  = 'aspx'

and D.iscurrentversion = '1'

and D.setuppath is NULL

 

Reference Links:

 

ALLDOCS table schema – columns and its description

http://msdn.microsoft.com/en-us/library/cc704495.aspx 

 

Database table information to find page customized status

http://sky-soft.net/SkySoft/Documentation/WSS/WSSv3Notes.htm

 

Information about SharePoint content database

http://www.sharepointu.com/ethan/archive/2007/09/16/inspecting-the-sharepoint-content-database.aspx

 

Query to find list of documents from site collection

http://stackoverflow.com/questions/213801/sharepoint-2007-sql-query-to-find-a-list-of-documents-in-site-collection

 

After finding my result from database, I thought to tried into SharePoint object model, here is link for finding page customization status from object model.