Some Useful Sharepoint 2007 Content Database queries

1- Query to find all form libraries in Sharepoint Farm

SELECT w.Title, w.FullUrl, AllLists.tp_Title
FROM AllLists INNER JOIN
Webs AS w ON AllLists.tp_WebId = w.Id
WHERE (AllLists.tp_ContentTypes LIKE '%Forms/Form%')
ORDER BY w.Title

2- Query to find list with specific content type – Content type is given in where clause
SELECT w.FullUrl, w.Title as Sitename ,A.tp_Title as Listname
FROM AllLists AS A INNER JOIN
Webs AS w ON A.tp_WebId = w.Id
WHERE (A.tp_ContentTypes LIKE '%PowerPoint%')
ORDER BY w.Title

3- Query to find access request detail on each site-

SELECT Title, FullUrl, RequestAccessEmail
FROM Webs
ORDER BY Title

4- Query to give list of alerts on a site – SiteUrl given in where clause

--- List Immediate Alert------

SELECT u.tp_login,u.tp_title,i.UserEmail, i.WebUrl, i.WebTitle, i.ListUrl, i.ListTitle, i.AlertTitle, i.Filter,i.properties
FROM userinfo u , ImmedSubscriptions i
WHERE (i.siteid=u.tp_siteid)and (u.tp_email=i.useremail)and (i.WebUrl LIKE '%Site Url as parameter %')and i.useremail<>' '
order by i.webtitle



--- List Daily/weekly Alert------


SELECT u.tp_login , u.tp_title,(select case notifyfreq when 1 then 'Daily'when 2 then 'Weekly' end ) as NotifyFrequency, s.useremail,s.weburl,s.webtitle , s.listurl,s.listtitle, s.alerttitle ,s.filter,s.properties FROM SchedSubscriptions s , userinfo u
WHERE ( s.useremail=u.tp_email)and (s.siteid=u.tp_siteid) and (s.WebUrl LIKE '%/Site Url as parameter %')
ORDER BY u.tp_login

5- Query to know list/libraries not inheriting permissions from parent site – Site Url given in where clause

Select distinct w.fullurl,w.title,al.tp_title,r.title
from webs w, alllists al ,roleassignment ra, roles r
where al.tp_hasfgp=1 and w.id=al.tp_webid and al.tp_scopeid=ra.scopeid and r.roleid=ra.roleid and w.fullurl like '%Site Url as parameter %'
order by w.fullurl
6- Query to find all workflows in Site collection-

Select distinct(w.fullurl),L.tp_Title from webs w,workflow wf , Alllists L where w.id=wf.webid and w.siteId=wf.siteId and wf.listId=L.tp_Id order by fullurl


7- Query to find all checked out document on sharepoint site-Site url is given in where clause

Select distinct d.dirname, d.leafname, d.version,d.extension, d.checkoutuserid, d.ischeckouttolocal ,u.tp_title from Docs D, USerInfo U where (d.checkoutuserid is not null) and (d.checkoutuserid=u.tp_ID) and d.siteID=u.tp_SIteID and d.iscurrentversion=0 and d.dirname like 'Site Url as parameter ' order by dirname


--To find Checked out document during specific time period--- dates need to be given in where clause
Select distinct d.dirname, d.leafname, d.version,d.extension, d.checkoutuserid, d.checkoutdate ,u.tp_title from Docs D, USerInfo U where (d.checkoutuserid is not null) and (d.checkoutuserid=u.tp_ID) and d.siteID=u.tp_SIteID and d.iscurrentversion=0 order by dirname
d.checkoutdate between to_date ('2009/06/15', 'yyyy/mm/dd')
AND to_date ('2009/06/22', 'yyyy/mm/dd')
8- Query to find user permissions on the list within the site

User Id need to be given as parameter
select w.fullurl, u.tp_login,u.tp_title,R.Title , al.tp_title
from webs w, UserInfo u, RoleAssignment RA, Roles R,Perms P , Alllists al
where w.id=al.tp_webid and al.tp_webid=p.WebID and al.tp_webid=r.webid
and al.tp_scopeid=ra.scopeid and w.id=p.WebID and
w.id=r.webid and r.webid=p.webid and ra.scopeid=P.scopeid
and u.tp_id=ra.principalid and r.roleid=ra.roleid and u.tp_login like 'User Id as a parameter'

9- Query to find permission roles on list in the Sharepoint site

Select distinct d.dirname, d.leafname, d.version,d.extension, d.checkoutuserid, d.ischeckouttolocal ,u.tp_title from Docs D, USerInfo U where (d.checkoutuserid is not null) and (d.checkoutuserid=u.tp_ID) and d.siteID=u.tp_SIteID and d.iscurrentversion=0 and d.dirname like 'Site Url as parameter ' order by dirname