Share point queries

It is a very common requirement in SharePoint Portal Solution to analyze a SharePoint solution in terms of storage capacity and statistics of sites, document libraries, area etc., to monitor the growth of the SharePoint solution and to take prompt action, if any thing goes wrong. Like, SharePoint should know what document library is most used in terms of versions, or the number of documents uploaded.

Microsoft SharePoint Portal Solution database is Microsoft�s proprietary database, and it cannot be modified because of any problems with future service pack releases, but sometimes you can write SELECT queries that can give full information that may be useful for the SharePoint Administrator. I was looking for some queries that would help me to analyze a SharePoint solution, but unfortunately, didn�t find any article that gives a list of useful queries, so I started to write my own SQL queries.

Contents
An Overview of the SharePoint Database Schema
A List of SQL Queries
An Overview of the SharePoint Database Schema
In order to write queries against the SharePoint database schema, you should how Microsoft organizes information in SQL tables. Some common tables are:

Docs
Docversions
Sites
Webs
Information pertaining to document libraries, list, area, and sites can easily be fetched by joining these tables in the Portal_Site database. You can write a query against the PORTAL_Profile database to get useful information of user profiles etc.

A List of SQL Queries
Top 100 documents in terms of size (latest version(s) only): Collapse | Copy CodeSELECT TOP 100 Webs.FullUrl As SiteUrl,
Webs.Title 'Document/List Library 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 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' DESCTop 100 most versioned documents: Collapse | Copy CodeSELECT TOP 100
Webs.FullUrl As SiteUrl,
Webs.Title 'Document/List Library Title',
DirName + '/' + LeafName AS 'Document Name',
COUNT(Docversions.version)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)' descList of unhosted pages in the SharePoint solution: Collapse | Copy Codeselect Webs.FullUrl As SiteUrl,
case when [dirname] = ''
then '/'+[leafname]
else '/'+[dirname]+'/'+[leafname]
end as [Page Url],
CAST((CAST(CAST(Size as decimal(10,2))/1024 As
decimal(10,2))/1024) AS Decimal(10,2)) AS 'File Size in MB'
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
where [type]=0
and [leafname] like ('%.aspx')
and [dirname] not like ('%_catalogs/%')
and [dirname] not like ('%/Forms')
and [content] is not null
and [dirname] not like ('%Lists/%')
and [setuppath] is not null
order by [Page Url];List of top level WSS sites and their total size, including child sites in the portal: Collapse | Copy Codeselect FullUrl As SiteUrl,
CAST((CAST(CAST(DiskUsed as decimal(10,2))/1024 As
decimal(10,2))/1024) AS Decimal(10,2)) AS 'Total Size in MB'
from sites
Where FullUrl LIKE '%sites%' AND
fullUrl <> 'MySite' AND fullUrl <> 'personal'
List of portal area and total number of users: Collapse | Copy Codeselect webs.FullUrl, Webs.Title,
COUNT(WebMembers.UserId) As 'Total User'
from Webs INNER JOIN WebMembers
ON Webs.Id = WebMembers.WebId
Where fullurl NOT like '%sites%' AND
fullUrl <> 'MySite' AND fullUrl <> 'personal'
Group BY webs.FullUrl, Webs.Title
Order By 'Total User' descList of top level and sub sites in the portal and the number of users: Collapse | Copy Codeselect webs.FullUrl ,Webs.Title, COUNT(WebMembers.UserId) As 'Total User'
from Webs INNER JOIN WebMembers
ON Webs.Id = WebMembers.WebId
where fullurl like '%sites%' AND fullUrl <> 'MySite' AND fullUrl <> 'personal'
Group BY webs.FullUrl, Webs.Title
Order By 'Total User' descList of all portal area: Collapse | Copy Codeselect Webs.FullUrl As [Site Url],
Title AS [Area Title]
from Webs
Where fullurl NOT like '%sites%' AND fullUrl <>
'MySite' AND fullUrl <> 'personal'List of the total portal area: Collapse | Copy Codeselect COUNT(*)from Webs
Where fullurl NOT like '%sites%' AND
fullUrl <> 'MySite' AND fullUrl <> 'personal'List of all top level and sub sites in the portal: Collapse | Copy Codeselect Webs.FullUrl As [Site Url],
Title AS [WSS Site Title]
from webs
where fullurl like '%sites%' AND fullUrl <>
'MySite' AND fullUrl <> 'personal'List of the total top level and sub sites in the portal: Collapse | Copy Codeselect COUNT(*) from webs
where fullurl like '%sites%' AND fullUrl <>
'MySite' AND fullUrl <> 'personal'List of all list/document libraries and total items: Collapse | Copy Codeselect
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 descNote: the tp_servertemplate field can have the following values:

104 = Announcement
105 = Contacts List
108 = Discussion Boards
101 = Document Library
106 = Events
100 = Generic List
1100 = Issue List
103 = Links List
109 = Image Library
115 = InfoPath Form Library
102 = Survey List
107 = Task List
List of document libraries and total items: Collapse | Copy Codeselect
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,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 101
order by tp_itemcount descList of image libraries and total items: Collapse | Copy Codeselect 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,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 109 -- Image Library
order by tp_itemcount descList of announcement list and total items: Collapse | Copy Codeselect 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,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 104 -- Announcement List
order by tp_itemcount descList of contact list and total items: Collapse | Copy Codeselect 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,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 105 -- Contact List
order by tp_itemcount descList of event list and total items: Collapse | Copy Codeselect 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,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 106 -- Event List
order by tp_itemcount descList of all tasks and total items: Collapse | Copy Codeselect
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,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 107 -- Task List
order by tp_itemcount descList of all InfoPath form library and total items: Collapse | Copy Codeselect
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,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 115 -- Infopath Library
order by tp_itemcount descList of generic list and total items: Collapse | Copy Codeselect
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,
tp_itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
Where tp_servertemplate = 100 -- Generic List
order by tp_itemcount descTotal number of documents: Collapse | Copy CodeSELECT 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 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 MS Word documents: Collapse | Copy CodeSELECT 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 LIKE '%.doc')
AND (LeafName NOT LIKE '%template%')Total MS Excel documents: Collapse | Copy CodeSELECT 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 LIKE '%.xls')
AND (LeafName NOT LIKE '%template%')Total MS PowerPoint documents: Collapse | Copy CodeSELECT 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 LIKE '%.ppt')
AND (LeafName NOT LIKE '%template%')Total TXT documents: Collapse | Copy CodeSELECT 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 LIKE '%.txt')
AND (LeafName NOT LIKE '%template%')Total Zip files: Collapse | Copy CodeSELECT 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 LIKE '%.zip')
AND (LeafName NOT LIKE '%template%')Total PDF files: Collapse | Copy CodeSELECT 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 LIKE '%.pdf')
AND (LeafName NOT LIKE '%template%')Total JPG files: Collapse | Copy CodeSELECT 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 LIKE '%.jpg')
AND (LeafName NOT LIKE '%template%')Total GIF files: Collapse | Copy CodeSELECT 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 LIKE '%.gif')
AND (LeafName NOT LIKE '%template%')Total files other than DOC, PDF, XLS, PPT, TXT, Zip, ASPX, DEWP, STP, CSS, JPG, GIF: Collapse | Copy CodeSELECT 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 NOT LIKE '%.pdf')
AND (LeafName NOT LIKE '%template%')
AND (LeafName NOT LIKE '%.doc')
AND (LeafName NOT LIKE '%.xls')
AND (LeafName NOT LIKE '%.ppt')
AND (LeafName NOT LIKE '%.txt')
AND (LeafName NOT LIKE '%.zip')
AND (LeafName NOT LIKE '%.aspx')
AND (LeafName NOT LIKE '%.dwp')
AND (LeafName NOT LIKE '%.stp')
AND (LeafName NOT LIKE '%.css')
AND (LeafName NOT LIKE '%.jpg')
AND (LeafName NOT LIKE '%.gif')
AND (LeafName <>'_webpartpage.htm')Total size of all documents: Collapse | Copy CodeSELECT 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 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')
AND (LeafName <>'_webpartpage.htm')Conclusion

How to edit the Display page of Events in Sharepoint 2003 Version

How can i edit the Display page of Events in Sharepoint 2003 Version ?

1) go To the folder "C:\Program Files\Common Files\Microsoft Shared\web server extensions\60\TEMPLATE\1033\MPS" where events related files present
2) Check for the Default.aspx

3) I have received a requirement during my experience to disable to "Go to Events" for every event page .

4) For every event page this page will loads so at the time of body load i have called a function to disable the "Go To Events" link and its image based on the control name rendered .
5) That's it we are ready .



add the function to the body onload as below

onload="MWSOnLoad();disableEventLink();"

Deleting users from all sites in SPS 2003

Deleting users from all sites in SPS 2003
By tpham
Sure, you can use the stsadm.exe tool with the “deleteuser” command, but you have to do that with *every* site which that user is a member of. And sure, you can use the “enumusers” switch to find these dead users, but again that is limited to listing the users for one site only.

So, fire up SQL Query Analyzer, connect to your SharePoint SQL database, and switch the active database to PORTALNAME_SITE. Then paste the following SQL:

SELECT DISTINCT N’”C:\Program Files\Common Files\Microsoft Shared\web server extensions\60\BIN\stsadm.exe”‘+ N’ -o deleteuser -url “http://portaladdress/’ + dbo.Webs.FullUrl + N’” -userlogin “‘ + dbo.UserInfo.tp_Login + N’”‘ AS Command FROM dbo.Webs RIGHT OUTER JOIN dbo.UserInfo ON dbo.Webs.SiteId = dbo.UserInfo.tp_SiteID WHERE (dbo.UserInfo.tp_Login = N’DOMAINNAME\username1′ OR dbo.UserInfo.tp_Login = N’ DOMAINNAME\username2′) AND (dbo.Webs.FullUrl IS NOT NULL) AND (dbo.Webs.FullUrl LIKE N’sites%’)

Of course, change the path to stsadm.exe if it is different for your server, replace http://portaladdress with your root portal address, and replace DOMAINNAME\username1 with the accounts that you want to remove. Then run the query, and it will generate one “stsadm –o deleteuser” command for every user and for every site which that user is a member of. Copy the output to the clipboard, paste it into Notepad and save it as a .bat file. Then run the .bat file in the Command Window using “ | clip”. The output will be silent but captured to the clipboard so you can review any possible errors after the operation is completed. Some errors that may occur include deleting the last administrator of a Site, missing users (I got lots of these but don’t know why yet) and deleting users from sub-sites in a Site collection that does not have unique permissions. All of these errors are harmless, but you’ll be left with a much cleaner collection of Site memberships in SharePoint, free of those dratted ex-employees whom you want to forget about and can now safely do so since their name won’t keep popping up anymore.

How To Make SharePoint 2003 Play Nice With Office 2007 Files

Office 2007 and SharePoint 2003, How To Fix Icons and File Open Issues
After digging around more than a dozen websites to get my own SharePoint site accepting Office 2007 files (docx, pptx, etc) as Word and PowerPoint files and not zip files I thought I’d try to bring all the info into one place for easy reference.
As you may of have not noticed if you’re using SharePoint 2003 and Office 2007 and save your documents to SharePoint you never see the correct icon and if you try to open the file from SharePoint it now thinks the file is a zip file. You can work around this by ignoring the icon and doing the little "Drop Down" and say "Edit in Word" but just because Microsoft has released SharePoint 2007 that doesn’t mean you have to do work arounds just to get your own site to work with Office 2007.
Since we are going to be working with IIS let’s go ahead and stop it, ‘net stop iisadmin’.
So first off let’s get the icons we need. A big shout out to Alexander for creating those icons for us. Just open the zip, grab the icons from the "gif-light" directory, copy them into ‘%program directory%\Common Files\Microsoft Shared\web server extensions\60\TEMPLATE\IMAGES’. Now just edit the XML file which loads those icons by going to, ‘%program directory\Common Files\Microsoft Shared\web server extensions\60\TEMPLATE\XML’, open "docicon.xml" with Notepad. ALERT: For me any time I start messing with windows files I expect Murphy to show up so do yourself a favor and before you edit this and other files do a right-click, send to, compressed file just in case. Locate the section section and add the following keeping your formatting inline for easy reference and nice XML:

















Now that we have the icons and SharePoint knows how to assign them let’s move on to getting IIS to understand that a .docx file should be opened by Word instead of Zip. This also is pretty straight forward once you either know IIS and knew immediately the problem was a mime type association or you keep reading and just let me tell you how to fix this. Basically the problem is IIS doesn’t know how to serve up these file extensions so we need to tell it how by adding the mime type associations. Since we stopped IIS earlier it’s just a matter of editing the XML file containing the extensions which is ‘MetaBase.XML’ located at, %Windows%\system32\inetsrv. What we need to do is add the following to the IIsMimeMap section. Again keep the formatting pretty and do the ‘send to compressed’ just in case. Also note there is a leading and trailing " in that section so be careful how you paste and edit :
.docm,application/vnd.ms-word.document.macroEnabled.12
.docx,application/vnd.openxmlformats-officedocument.wordprocessingml.document
.dotm,application/vnd.ms-word.template.macroEnabled.12
.dotx,application/vnd.openxmlformats-officedocument.wordprocessingml.template
.potm,application/vnd.ms-powerpoint.template.macroEnabled.12
.potx,application/vnd.openxmlformats-officedocument.presentationml.template
.ppam,application/vnd.ms-powerpoint.addin.macroEnabled.12
.ppsm,application/vnd.ms-powerpoint.slideshow.macroEnabled.12
.ppsx,application/vnd.openxmlformats-officedocument.presentationml.slideshow
.pptm,application/vnd.ms-powerpoint.presentation.macroEnabled.12
.pptx,application/vnd.openxmlformats-officedocument.presentationml.presentation
.xlam,application/vnd.ms-excel.addin.macroEnabled.12
.xlsb,application/vnd.ms-excel.sheet.binary.macroEnabled.12
.xlsm,application/vnd.ms-excel.sheet.macroEnabled.12
.xlsx,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
.xltm,application/vnd.ms-excel.template.macroEnabled.12
.xltx,application/vnd.openxmlformats-officedocument.spreadsheetml.template

Now just start IIS back up, simple way for that is to do an ‘IISReset’ at the command prompt and go test SharePoint. If you have followed the above correctly and not made any editing mistakes to the XML files you should be good to go.