Tuesday, September 17, 2013

SQL Queries for SharePoint Content Database

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 retried just as any other SQL database.Here are some useful queries that will help you. These queries you can execute in your SharePoint Content Database. Each WebApplication of your SharePoint will hold an Content Database.

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


Below is the Content Database for one of the WebApplication



When you will open the SharePoint Database you will see a set of tables.


Below are some of the basic tables within a content database and a very high level diagram on some of the relationships between them.
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.
All Lists
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.
Sched Subscriptions
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.


SharePointContentDatabaseModel
 

Some of the common queries that can be used against the content database:
--Returns Total Number of Site Collections in WebApplication
select  count(*) as 'Total Site Collection' from sites
--Returns Root Site Title for each Site Collection available in WebApplication
 select Title as 'Root Web Title', Sites.RootWebId, Sites.Id as 'Site Collection ID' from webs
 inner join Sites on Webs.Id = Sites.RootWebId
 --Returns Total Web Sites in WebApplication
select count(*) from Webs
--Returns WebSite Title and Site Id
select Title as 'Site title',FullUrl, SiteId as 'Site Collection Id' from Webs order by SiteId
--Returns Total number of Web Sites under each SiteCollection
select SiteId, count(*) as 'Total Sub Sites' from Webs inner join Sites on Sites.Id = Webs.SiteId group by SiteId
--Returns Total number of Web Sites under each SiteCollection for 'Doc Lib' and 'Form Lib'
SELECT
"Template Type" = CASE
WHEN [Lists].[tp_ServerTemplate] = 101 THEN 'Doc Lib'
WHEN [Lists].[tp_ServerTemplate] = 115 THEN 'Form Lib'
ELSE 'Unknown'
END,
"List URL" = 'http://mlaw/' + CASE
WHEN [Webs].[FullUrl]=''
THEN [Webs].[FullUrl] + [Lists].[tp_Title]
ELSE [Webs].[FullUrl] + '/' + [Lists].[tp_Title]
END,
"Template URL" = 'http://mlaw/' +
[Docs].[DirName] + '/' + [Docs].[LeafName]
FROM [Lists] LEFT OUTER JOIN [Docs] ON [Lists].[tp_Template]=[Docs].[Id], [Webs]
WHERE ([Lists].[tp_ServerTemplate] = 101 OR [Lists].[tp_ServerTemplate] = 115)
AND [Lists].[tp_WebId]=[Webs].[Id]
order by "List URL"
--Returns Total number of Web Sites under each SiteCollection for 'Doc Lib' Only
SELECT
"Template Type" = CASE
WHEN [Lists].[tp_ServerTemplate] = 101 THEN 'Doc Lib'
WHEN [Lists].[tp_ServerTemplate] = 115 THEN 'Form Lib'
ELSE 'Unknown'
END,
"List URL" = 'http://mlaw/' + CASE
WHEN [Webs].[FullUrl]=''
THEN [Webs].[FullUrl] + [Lists].[tp_Title]
ELSE [Webs].[FullUrl] + '/' + [Lists].[tp_Title]
END,
"Template URL" = 'http://mlaw/' +
[Docs].[DirName] + '/' + [Docs].[LeafName]
FROM [Lists] LEFT OUTER JOIN [Docs] ON [Lists].[tp_Template]=[Docs].[Id], [Webs]
WHERE ([Lists].[tp_ServerTemplate] = 101)
AND [Lists].[tp_WebId]=[Webs].[Id]
order by "List URL"
--query to get count of documents from site collection for 2010
select
SUM(itemcount) As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
inner join AllListsAux ON Lists.tp_ID = AllListsAux.ListID
Where tp_servertemplate = 101
select
case when webs.fullurl = ''
then 'Portal Site'
else webs.fullurl
end as [Site Relative Url],
webs.Title As [Site Title],
lists.tp_title As Title,
tp_description As Description,
itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
inner join AllListsAux ON Lists.tp_ID = AllListsAux.ListID
Where tp_servertemplate = 101
Order By [Site Relative Url]
for 2007
SELECT CASE WHEN webs.fullurl = ''
THEN 'Portal Site'
ELSE webs.fullurl
END AS [Site Relative Url],
Webs.Title AS [Site Title],
CASE tp_servertemplate
WHEN 104 THEN 'Announcement'
WHEN 105 THEN 'Contacts'
WHEN 108 THEN 'Discussion Boards'
WHEN 101 THEN 'Docuemnt Library'
WHEN 106 THEN 'Events'
WHEN 100 THEN 'Generic List'
WHEN 1100 THEN 'Issue List'
WHEN 103 THEN 'Links List'
WHEN 109 THEN 'Image Library'
WHEN 115 THEN 'InfoPath Form Library'
WHEN 102 THEN 'Survey'
WHEN 107 THEN 'Task List'
ELSE 'Other' END AS TYPE,
tp_title 'Title',
tp_description AS [Description],
tp_itemcount AS [Total Item]
FROM lists inner join webs ON lists.tp_webid = webs.Id
WHERE tp_servertemplate IN (104,105,108,101,
      106,100,1100,103,109,115,102,107,120)
ORDER BY tp_itemcount DESC
-- 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 


====================================================================
-- Query to get all the users assigned to roles
SELECT DISTINCT
CASE WHEN PATINDEX('%\%', FullUrl) > 0 THEN LEFT(FullUrl, PATINDEX('%\%', FullUrl) - 1) ELSE FullUrl END AS [Site],
Webs.Title,
Webs.FullUrl,
Perms.ScopeUrl,
UserInfo.tp_Login As Account,
CASE WHEN UserInfo.tp_DomainGroup>0 THEN NULL ELSE UserInfo.tp_Title END AS Username,
CASE WHEN UserInfo.tp_DomainGroup>0 THEN UserInfo.tp_Login ELSE NULL END AS [AD Group],
NULL AS [SharePoint Group],
Roles.Title AS RoleTitle,
Roles.PermMask
FROM
dbo.RoleAssignment
INNER JOIN dbo.UserInfo ON RoleAssignment.SiteId = UserInfo.tp_SiteID AND UserInfo.tp_ID = RoleAssignment.PrincipalId
INNER JOIN dbo.Perms ON Perms.SiteId = RoleAssignment.SiteId AND Perms.ScopeId = RoleAssignment.ScopeId
INNER JOIN dbo.Roles ON RoleAssignment.SiteId = Roles.SiteId AND RoleAssignment.RoleId = Roles.RoleId
INNER JOIN dbo.Webs ON Roles.SiteId = Webs.SiteId AND Roles.WebId = Webs.Id
WHERE
Roles.Type<>1 AND tp_Deleted=0
UNION
=============================================================================
-- Query to get all the SharePoint groups assigned to roles
SELECT DISTINCT
CASE WHEN PATINDEX('%\%', FullUrl) > 0 THEN LEFT(FullUrl, PATINDEX('%\%', FullUrl) - 1) ELSE FullUrl END AS [Site],
Webs.Title,
Webs.FullUrl,
Perms.ScopeUrl,
UserInfo.tp_Login As Account,
CASE WHEN UserInfo.tp_DomainGroup>0 THEN NULL ELSE UserInfo.tp_Title END AS Username,
CASE WHEN UserInfo.tp_DomainGroup>0 THEN UserInfo.tp_Login ELSE NULL END AS [AD Group],
Groups.Title AS [SharePoint Group],
Roles.Title AS RoleTitle,
Roles.PermMask
FROM
dbo.RoleAssignment
INNER JOIN dbo.Roles ON RoleAssignment.SiteId = Roles.SiteId AND RoleAssignment.RoleId = Roles.RoleId
INNER JOIN dbo.Perms ON Perms.SiteId = RoleAssignment.SiteId AND Perms.ScopeId = RoleAssignment.ScopeId
INNER JOIN dbo.Webs ON Roles.SiteId = Webs.SiteId AND Roles.WebId = Webs.Id
INNER JOIN dbo.Groups ON RoleAssignment.SiteId = Groups.SiteId AND RoleAssignment.PrincipalId = Groups.ID
INNER JOIN dbo.GroupMembership ON GroupMembership.SiteId = Groups.SiteId AND GroupMembership.GroupId = Groups.ID
INNER JOIN dbo.UserInfo ON GroupMembership.SiteId = UserInfo.tp_SiteID AND GroupMembership.MemberId = UserInfo.tp_ID
WHERE
Roles.Type<>1 AND tp_Deleted=0
===============================================================
-- Top 100 Documents that is versioned based on doc size
SELECT TOP 100
Webs.FullUrl As SiteUrl,
Webs.Title 'Document/List Library Title',
DirName + '/' + LeafName AS 'Document Name',
COUNT(Docversions.UIVersion)AS 'Total Version',
SUM(CAST((CAST(CAST(Docversions.Size as decimal(10,2))/1024 As
   decimal(10,2))/1024) AS Decimal(10,2)) )  AS  'Total Document Size (MB)',
CAST((CAST(CAST(AVG(Docversions.Size) as decimal(10,2))/1024 As
   decimal(10,2))/1024) AS Decimal(10,2))   AS  'Avg Document Size (MB)'
FROM Docs INNER JOIN DocVersions ON Docs.Id = DocVersions.Id
   INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1
AND (LeafName NOT LIKE '%.stp') 
AND (LeafName NOT LIKE '%.aspx') 
AND (LeafName NOT LIKE '%.xfp')
AND (LeafName NOT LIKE '%.dwp')
AND (LeafName NOT LIKE '%template%')
AND (LeafName NOT LIKE '%.inf')
AND (LeafName NOT LIKE '%.css')
GROUP BY Webs.FullUrl, Webs.Title, DirName + '/' + LeafName
ORDER BY 'Total Version' desc, 'Total Document Size (MB)' desc

Note: When the document is in "Draft" state, if you use the above query you will be getting the count of versions in double. Ex:if total version count is 3, when the doc is in Draft state if you use the above query the count will return as 6.
============================================================================
Retrieve the file details from AllDocs Database. Which has the information about the files stored in SharePoint List or Library.
– Returns all document from all lists availabe in WebApplication
SELECT AllDocs.Leafname AS FileName’,
                 AllDOcs.Dirname AS ‘Folder Path’,
                 AllLists.tp_Title AS ‘List Title’,
                 Webs.Title AS ‘Web Title’
FROM AllDocs
JOIN AllLists
ON
AllLists.tp_Id=AllDocs.ListId
JOIN Webs
ON
Webs.Id=AllLists.tp_WebId
ORDER BY
webs.title


If you need the file informations about particular document type. Use the Extension column to check the document type.
For Ex., The following Query returns only master pages on all WebSites,
– Returns master pages in WebApplication for all WebSites
SELECT AllDocs.Leafname AS FileName’,
                 AllDocs.Dirname AS ‘Folder Path’,
                 AllLists.tp_Title AS ‘List Title’,
                 Webs.Title AS ‘Web Title’
FROM AllDocs
JOIN AllLists
ON
AllLists.tp_Id=AllDocs.ListId
JOIN Webs
ON
Webs.Id=AllLists.tp_WebId
WHERE Extension=’master’

ORDER BY Webs.Title
=========================================================================
simple SQL Query to retrieve the Document List Name,File Name, URL, and the Content (Binary Format)
  SELECT AllLists.tp_Title AS ‘List Name’,
AllDocs.LeafName AS ‘File Name’,
AllDocs.DirName AS ‘URL’,
AllDocStreams.Content AS ‘Document Contnt (Binary)’
FROM AllDocs
JOIN AllDocStreams
ON
AllDocs.Id=AllDocStreams.Id
JOIN AllLists
ON
AllLists.tp_id = AllDocs.ListId
==================================================================================
Get Documents By Age
SELECT Webs.FullUrl AS SiteUrl, Webs.Title AS [Title], DirName + ‘/’ + LeafName AS [Document Name], Docs.TimeCreated
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
WHERE Docs.Type <> 1
AND (LeafName IS NOT NULL)
AND (LeafName <> )
AND (LeafName NOT LIKE ‘%.stp’)
AND (LeafName NOT LIKE ‘%.aspx’)
AND (LeafName NOT LIKE ‘%.xfp’)
AND (LeafName NOT LIKE ‘%.dwp’)
AND (LeafName NOT LIKE ‘%template%’)
AND (LeafName NOT LIKE ‘%.inf’)
AND (LeafName NOT LIKE ‘%.css’)
ORDER BY Docs.TimeCreated DESC
==============================================================
Total # of Documents
SELECT COUNT(*)
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
WHERE Docs.Type <> 1
AND (LeafName IS NOT NULL)
AND (LeafName <> )
AND (LeafName NOT LIKE ‘%.stp’)
AND (LeafName NOT LIKE ‘%.aspx’)
AND (LeafName NOT LIKE ‘%.xfp’)
AND (LeafName NOT LIKE ‘%.dwp’)
AND (LeafName NOT LIKE ‘%template%’)
AND (LeafName NOT LIKE ‘%.inf’)
AND (LeafName NOT LIKE ‘%.css’)
 
================================================================
Total Size of All Content
SELECT SUM(CAST((CAST(CAST(Size AS DECIMAL(10,2))/1024 AS DECIMAL(10,2))/1024) AS DECIMAL(10,2))) AS ‘Total Size in MB’
FROM Docs INNER JOIN Webs ON Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
WHERE Docs.Type <> 1
AND (LeafName IS NOT NULL)
AND (LeafName NOT LIKE ‘%.stp’)
AND (LeafName NOT LIKE ‘%.aspx’)
AND (LeafName NOT LIKE ‘%.xfp’)
AND (LeafName NOT LIKE ‘%.dwp’)
AND (LeafName NOT LIKE ‘%template%’)
AND (LeafName NOT LIKE ‘%.inf’)
AND (LeafName NOT LIKE ‘%.css’)
====================================================================
Get Documents by Size
SELECT TOP 100 Webs.FullUrl AS SiteUrl, Webs.Title AS [Title], DirName + ‘/’ + LeafName AS [Document Name], CAST((CAST(CAST(Size AS DECIMAL(10,2))/1024 AS DECIMAL(10,2))/1024) AS DECIMAL(10,2)) AS ‘Size in MB’
FROM Docs INNER JOIN Webs ON Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
WHERE Docs.Type <> 1
AND (LeafName IS NOT NULL)
AND (LeafName <> )
AND (LeafName NOT LIKE ‘%.stp’)
AND (LeafName NOT LIKE ‘%.aspx’)
AND (LeafName NOT LIKE ‘%.xfp’)
AND (LeafName NOT LIKE ‘%.dwp’)
AND (LeafName NOT LIKE ‘%template%’)
AND (LeafName NOT LIKE ‘%.inf’)
AND (LeafName NOT LIKE ‘%.css’)
ORDER BY ‘Size in MB’ DESC
===========================================================
Totals for each type of Document (DOCX/XLSX/PPTX/HTML)
If you want to check for others just reuse a select statement below with the additional file extension you want to track.

SELECT COUNT(*) AS “# of .DOCX”
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
WHERE Docs.Type <> 1
AND (LeafName LIKE ‘%.docx’)
AND (LeafName NOT LIKE ‘%template%’)
 
SELECT COUNT(*) AS “# of .PPTX”
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
WHERE Docs.Type <> 1
AND (LeafName LIKE ‘%.pptx’)
AND (LeafName NOT LIKE ‘%template%’)
 
SELECT COUNT(*) AS “# of .XLSX”
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
WHERE Docs.Type <> 1
AND (LeafName LIKE ‘%.xlsx’)
AND (LeafName NOT LIKE ‘%template%’)
 
SELECT COUNT(*) AS “# of .HTML”
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
WHERE Docs.Type <> 1
AND (LeafName LIKE ‘%.html’)
AND (LeafName NOT LIKE ‘%template%’) -r

9 comments:

  1. Hi Demant:

    Thank you very much for sharing this. I need to list all documents in our project sites in Project Server 2010 and this is going to help me a lot.

    Best regards

    ReplyDelete
  2. Hi, this post is very interesting! i'd like to know how to query which groups or people have access to documents :) (i mean which documents have permissions to see n-user or groups).

    Best regards

    ReplyDelete
    Replies
    1. Hi - I'm looking for the same info and wondering if you ever figured out a way to pull this?

      Delete
    2. Hi,

      I am also looking for same information.

      Delete
  3. hi, I need to get "Document ID" for all documents.

    ReplyDelete
  4. Thank you very very much for this post...

    ReplyDelete
  5. excellent post :) Thanks very much

    ReplyDelete