Monday, June 9, 2008

SQL Queries for SharePoint Content Database

Hi All,

The Sharepoint Content Database build up on SQL Server and has a good set of Tables.
These can be accessed and the data can be retrived just as any other SQL database.
Here are some useful queries that will help you.

NOTE: Never update any SharePoint database directly. Always use the SharePoint API (Object Model) for any updates.


When you will open the Sharepoint Database you will see a set of tables.Eg the ImmedSubscriptions table Ref the screen shot below.



We will begin with some of the basic tables.

Features Table that holds information about all the activated features for each site collection or site.

Sites Table that holds information about all the site collections for this content database.

Webs Table that holds information about all the specific sites (webs) in each site collection.

UserInfo Table that holds information about all the users for each site collection.

Groups Table that holds information about all the SharePoint groups in each site collection.

Roles Table that holds information about all the SharePoint roles (permission levels) for each site.

AllLists Table that holds information about lists for each site.

GroupMembership Table that holds information about all the SharePoint group members.

AllUserData Table that holds information about all the list items for each list.

AllDocs Table that holds information about all the documents (and all list items) for each document library and list.

RoleAssignment Table that holds information about all the users or SharePoint groups that are assigned to roles.

SchedSubscriptions Table that holds information about all the scheduled subscriptions (alerts) for each user.

ImmedSubscriptions Table that holds information about all the immediate subscriptions (alerts) for each user.



Some of the common queries that can be used against the content database:

-- Query to get all the top level site collections
SELECT SiteId AS SiteGuid, Id AS WebGuid, FullUrl AS Url, Title, Author, TimeCreated
FROM dbo.Webs
WHERE (ParentWebId IS NULL)

-- Query to get all the child sites in a site collection
SELECT SiteId AS SiteGuid, Id AS WebGuid, FullUrl AS Url, Title, Author, TimeCreated
FROM dbo.Webs
WHERE (NOT (ParentWebId IS NULL))

-- Query to get all the SharePoint groups in a site collection
SELECT dbo.Webs.SiteId, dbo.Webs.Id, dbo.Webs.FullUrl, dbo.Webs.Title, dbo.Groups.ID AS Expr1,
dbo.Groups.Title AS Expr2, dbo.Groups.Description
FROM dbo.Groups INNER JOIN
dbo.Webs ON dbo.Groups.SiteId = dbo.Webs.SiteId

-- Query to get all the users in a site collection
SELECT dbo.Webs.SiteId, dbo.Webs.Id, dbo.Webs.FullUrl, dbo.Webs.Title, dbo.UserInfo.tp_ID,
dbo.UserInfo.tp_DomainGroup, dbo.UserInfo.tp_SiteAdmin, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Email
FROM dbo.UserInfo INNER JOIN
dbo.Webs ON dbo.UserInfo.tp_SiteID = dbo.Webs.SiteId

-- Query to get all the members of the SharePoint Groups
SELECT dbo.Groups.ID, dbo.Groups.Title, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login
FROM dbo.GroupMembership INNER JOIN
dbo.Groups ON dbo.GroupMembership.SiteId = dbo.Groups.SiteId INNER JOIN
dbo.UserInfo ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID

-- Query to get all the sites where a specific feature is activated
SELECT dbo.Webs.Id AS WebGuid, dbo.Webs.Title AS WebTitle, dbo.Webs.FullUrl AS WebUrl, dbo.Features.FeatureId,
dbo.Features.TimeActivated
FROM dbo.Features INNER JOIN
dbo.Webs ON dbo.Features.SiteId = dbo.Webs.SiteId AND dbo.Features.WebId = dbo.Webs.Id
WHERE (dbo.Features.FeatureId = '00BFEA71-D1CE-42de-9C63-A44004CE0104')

-- Query to get all the users assigned to roles
SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle,
dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login
FROM dbo.RoleAssignment INNER JOIN
dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND
dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN
dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN
dbo.UserInfo ON dbo.RoleAssignment.PrincipalId = dbo.UserInfo.tp_ID

-- Query to get all the SharePoint groups assigned to roles
SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle,
dbo.Groups.Title AS GroupName
FROM dbo.RoleAssignment INNER JOIN
dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND
dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN
dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN
dbo.Groups ON dbo.RoleAssignment.SiteId = dbo.Groups.SiteId AND
dbo.RoleAssignment.PrincipalId = dbo.Groups.ID


If you come accross any useful queries do let me know.

9 comments:

Anonymous said...

When I fire these Queries in My SQL Server 2k5. I am getting following error.

Msg 33002, Level 16, State 1, Line 2
Access to table dbo.Webs is blocked because the signature is not valid.
Msg 33002, Level 16, State 1, Line 7
Access to table dbo.Webs is blocked because the signature is not valid.
Msg 33002, Level 16, State 1, Line 12
Access to table dbo.Webs is blocked because the signature is not valid.
Msg 33002, Level 16, State 1, Line 18
Access to table dbo.Webs is blocked because the signature is not valid.
Msg 33002, Level 16, State 1, Line 24
Access to table dbo.UserInfo is blocked because the signature is not valid.
Msg 33002, Level 16, State 1, Line 30
Access to table dbo.Webs is blocked because the signature is not valid.
Msg 33002, Level 16, State 1, Line 37
Access to table dbo.UserInfo is blocked because the signature is not valid.
Msg 33002, Level 16, State 1, Line 46
Access to table dbo.Groups is blocked because the signature is not valid.

Thanks in advance,
Bob

Manoj Iyer said...

Hi Bob,

From my analysis it seems to be a fault in the installation or
The database is corrupted.

Anyways can you give a detail of the environment at your end.

Please do answer the below:
1)Is the scenario of this type?
Access to table dbo.<any table I choose> is blocked because the signature is not valid ?

2)Are you using a Beta/Trial version of Windows SharePoint Services (WSS) ?

3)Do you have the rights of db_owner when you are executing the query on the database ?

Plese leave your email ID so that we can get back to you.

Thanks & regards.
sharepointkings@gmail.com

Anonymous said...

Hi,
I have initially database in SQL Server 2000 with WSS 3.0 when I have restored this site to another server and I tried to fire these Queries then it gives me error.
I have all admin rights.what I did is I have restore wss 3.0 site having data in SQL Server 2k to new server with WSS3.0 With Default Microsoft##SSEE database which come free with WSS3.0.

Manoj Iyer said...

Hi Bob,

Please go throught the article/KB and you will get the solution in it. Here the description for Event ID ( 33002 ) in Source ( MSSQL$MICROSOFT##SSEE ) is provided.

http://support.microsoft.com/kb/944154

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2272416&SiteID=17

There are case where people have installed the hotfix and got into this issues even.

http://www.eggheadcafe.com/software/aspnet/31021016/kb932091-ruined-my-wss-3.aspx

http://www.eggheadcafe.com/software/aspnet/31062991/cannot-connect-sharepoint.aspx

Thanks & Regards
sharepointkings@gmail.com

Jayesh Prajapati said...

Hi Bob,
Finally I have simulate your problem in my machine.I have same error that you are getting.But when I restore the same database in SQL Express 2k5 these Queries works well.as you said you have database from SQL 2k. upgrade process is perform while you restore site from backup.But so far Microsoft##SSEE failed to upgrade it.
to make it work I suggest you to work with SQL Express if your database has less than 4GB size.It works well with it.
Thanks & regards
sharepointkings@gmail.com

Jayesh Prajapati said...

Hi,
If you have WSS 3.0 with SQL Server 2000 and want to backup this site and restore your site to new server with WSS 3.0 and Microsoft##SSEE which comes free with WSS3.0,Do following things...
As there are two overloads for taking backup
1. stsadm -o backup -url "url" -filename "filename.bak" [-overwrite]

2. stsadm -o backup -directory "UNC path" -backupmethod
"full"

First method of taking backup will work well in this case..
Now hopefuly your site will restore and you will not have any error while fire Queries.

Anonymous said...

Love your blog. Have what I thought was a simple question. I want to create queries that reach into my content database and pull out data that I have stored in calendar lists. The trouble I am having is that I can only get one-time calendar items, I can't get recurring or all-day events.

K.D.Kadyan said...

Hi,

I am able to get all data from UserData but having one issue that is ItemID.

Please tell me how can i get the ID of the Item from tables.

Kuldeep Kadyan

Rob said...

Hi I found your post very useful. I often query the userinfo and webs tables. Do you know how to query the last login time? I want to know the last time my users access the system?




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