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

How to make common fields of two form share the column in same Infopath Form Library

1- Create two forms with common fields in design view. Example Form1 with field txta, txtb and Form2 with field txta , txtc.
2- Publish both of them as Site Content Type on the site and store the form templates in a document library. This step will also create the site columns specific to items within the form.
3- Create a form library which needs to share the columns between two forms. Allow management of content types for this form library.
4- Attach both Forms as content type in this form library.
5- Now publish the second form (Form2) to this existing form library as template to the document library. It will wipe out the Form1 specific columns (txtb) from the first content type form.
6- Now add the missing column from existing site columns.
7- Give the proper reference of first content type from existing document library.