Igor Feldman Sharepoint WIKIpedia

Please login or register.

Login with username, password and session length
Advanced search  

News:

SMF - Just Installed!

Author Topic: SQL Queries for SharePoint Content Database  (Read 628 times)

Administrator

  • Administrator
  • Newbie
  • *****
  • Karma: +0/-0
  • Posts: 9
    • View Profile
SQL Queries for SharePoint Content Database
« on: March 09, 2018, 08:31:12 PM »

https://demantprasad.wordpress.com/category/useful-sql-queries-for-sharepoint-content-database/


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

Wheretp_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],

CASEtp_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

WHEREtp_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.Type1 ANDtp_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.Type1 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 JOINDocVersions 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
Logged