|
Thanks for putting this up, its been really helpful.
Just one question though. In your code to get the Top 100 documents in terms of size (below), is there a way to add the 'TimeCreated' and 'TimeLastModified' columns?
I tried adding them but kept getting the error "Invalid Column Name" even though they do exist in the tables.
SELECT 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' DESC
|
|
|
|
|
Hello, I have a sharepoint 2003 site collection with many sites and subsites and I have to extract from the sql database a list of all the sites with all the users of the site. Could you help me with this? Thank you very much in advance, Alex
|
|
|
|
|
Hi,
These are great! Do you have anything similar for Sharepoint 2010?
Thanks
Jackie
|
|
|
|
|
Hi, i am trying to find the average size of documents in a site collection.
I have customised the code slightly.. it works fine on some site collcetions, but on other site collection i get the error
'Arithmetic overflow error converting int to data type numeric'
What does this mean?
Here is my code.
|
|
|
|
|
Nice Article...
I am a complete beginner in SharePoint, and I don't understand where to write these SQL queries.
What I want to do is to list all the documents in each site. I came across this article, and now I know the SQL queries, but just donno where to write them.
Do I write them in a web - part or some other place? If I have to use a web part which web part is to be used? I am using SharePoint Foundation that does not have the Content Query web part.
Please help.
Thanks in advance.
|
|
|
|
|
--------------------------------------------------------------------------------------------
-- Top 100 documents en termes de taille (dernière(s) version(s) seulement) :
--------------------------------------------------------------------------------------------
SELECT TOP 100 Webs.FullUrl AS SiteUrl, Webs.Title AS 'Document/List Library Title',
DirName + '/' + LeafName AS 'Nom Document',
CAST((CAST(Size AS DECIMAL(12,2))/1024000) As DECIMAL(12,2)) AS 'Taille (MB)'
FROM AllDocs
INNER JOIN Webs On AllDocs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE AllDocs.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 'Taille (MB)' DESC
--------------------------------------------------------------------------------------------
-- Top 100 des documents avec le plus de versions
--------------------------------------------------------------------------------------------
SELECT TOP 100 Webs.FullUrl AS SiteUrl,
Webs.Title AS 'Document/List Library Title',
DirName + '/' + LeafName AS 'Nom Document',
COUNT(Docversions.version)AS 'Total Version',
SUM(CAST((CAST(Docversions.Size AS DECIMAL(12,2))/1024000) As DECIMAL(12,2) ) ) AS 'Total Taille Document (MB)',
CAST((CAST(AVG(Docversions.Size) AS DECIMAL(12,2))/1024000) As DECIMAL(12,2) ) AS 'Moy Taille Document (MB)'
FROM AllDocs
INNER JOIN DocVersions ON AllDocs.Id = DocVersions.Id
INNER JOIN Webs On AllDocs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE AllDocs.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 4 DESC, 5 DESC
--------------------------------------------------------------------------------------------
-- Liste des pages non hébergées dans la solution SharePoint
--------------------------------------------------------------------------------------------
SELECT Webs.FullUrl AS SiteUrl,
CASE WHEN [dirname] = ''
THEN '/'+[leafname]
ELSE '/'+[dirname]+'/'+[leafname]
END AS [Page Url],
CAST((CAST(Size AS DECIMAL(12,2)) / 1024) AS DECIMAL(12,2)) AS 'Taille (KB)'
FROM AllDocs
INNER JOIN Webs On AllDocs.WebId = Webs.Id
WHERE [type]=0
AND [leafname] like ('%.aspx')
AND [dirname] NOT LIKE ('%_catalogs/%')
AND [dirname] NOT LIKE ('%/Forms')
AND [size] IS NOT NULL
AND [dirname] NOT LIKE ('%Lists/%')
AND [setuppath] IS NOT NULL
ORDER BY [Page Url];
--------------------------------------------------------------------------------------------
-- Liste des sites et de leur taille totale, sous-sites inclus
--------------------------------------------------------------------------------------------
SELECT Webs.Title, Webs.Description,
CAST (( CAST(Sites.DiskUsed AS DECIMAL(12,2))/ 1024000) AS DECIMAL(12,2) ) AS 'Total Taille (MB)'
FROM Sites, Webs
WHERE Sites.Id=Webs.SiteId
AND Webs.FullUrl = ''
ORDER BY Webs.FullUrl
--------------------------------------------------------------------------------------------
-- Liste du nombre total d'utilisateurs par zone du portail
--------------------------------------------------------------------------------------------
SELECT webs.FullUrl, Webs.Title, COUNT(WebMembers.UserId) AS 'Utilisateur(s)'
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 3 DESC
--------------------------------------------------------------------------------------------
-- Liste du nombre d'utilisateurs par sous-sites du portail
--------------------------------------------------------------------------------------------
SELECT webs.FullUrl ,Webs.Title, COUNT(WebMembers.UserId) AS 'Utilisateur(s)'
FROM Webs
INNER JOIN WebMembers ON Webs.Id = WebMembers.WebId
GROUP BY webs.FullUrl, Webs.Title
ORDER BY 3 DESC
--------------------------------------------------------------------------------------------
--Liste de toutes les zones du portail
--------------------------------------------------------------------------------------------
SELECT Webs.FullUrl AS [Site Url],
Title AS [Zone]
FROM Webs
order by 1
--------------------------------------------------------------------------------------------
-- Liste du nombre de zones du portail
--------------------------------------------------------------------------------------------
SELECT COUNT(*)FROM Webs
--------------------------------------------------------------------------------------------
-- Liste du nombre total d'éléments par liste/bibliothèque de documents
--------------------------------------------------------------------------------------------
-- Le champ "tp_servertemplate" peut avoir les valeurs suivantes : :
-- 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
--------------------------------------------------------------------------------------------
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 100 THEN 'Generic List'
WHEN 101 THEN 'Docuemnt Library'
WHEN 102 THEN 'Survey'
WHEN 103 THEN 'Links List'
WHEN 104 THEN 'Announcement'
WHEN 105 THEN 'Contacts'
WHEN 106 THEN 'Events'
WHEN 107 THEN 'Task List'
WHEN 108 THEN 'Discussion Boards'
WHEN 109 THEN 'Image Library'
WHEN 115 THEN 'InfoPath Form Library'
WHEN 1100 THEN 'Issue 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 (100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 115, 120, 1100)
ORDER BY tp_itemcount desc
--------------------------------------------------------------------------------------------
-- Liste du nombre total d'éléments par bibliothèque de documents
--------------------------------------------------------------------------------------------
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,
tp_itemcount AS [Total Item]
FROM lists inner join webs ON lists.tp_webid = webs.Id
WHERE tp_servertemplate = 101
ORDER BY tp_itemcount DESC
--------------------------------------------------------------------------------------------
-- Liste du nombre total d'éléments par bibliothèque d'images
--------------------------------------------------------------------------------------------
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,
tp_itemcount AS [Total Item]
FROM lists inner join webs ON lists.tp_webid = webs.Id
WHERE tp_servertemplate = 109
ORDER BY tp_itemcount DESC
--------------------------------------------------------------------------------------------
-- Liste du nombre total d'éléments par liste d'annonces
--------------------------------------------------------------------------------------------
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,
tp_itemcount AS [Total Item]
FROM lists inner join webs ON lists.tp_webid = webs.Id
WHERE tp_servertemplate = 104
ORDER BY tp_itemcount DESC
--------------------------------------------------------------------------------------------
-- Liste du nombre total d'éléments par liste de contacts:
--------------------------------------------------------------------------------------------
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,
tp_itemcount AS [Total Item]
FROM lists inner join webs ON lists.tp_webid = webs.Id
WHERE tp_servertemplate = 105
ORDER BY tp_itemcount DESC
--------------------------------------------------------------------------------------------
-- Liste du nombre total d'éléments par liste d'évènements
--------------------------------------------------------------------------------------------
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,
tp_itemcount AS [Total Item]
FROM lists inner join webs ON lists.tp_webid = webs.Id
WHERE tp_servertemplate = 106
ORDER BY tp_itemcount DESC
--------------------------------------------------------------------------------------------
-- Liste du nombre total d'éléments par liste de tâches
--------------------------------------------------------------------------------------------
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,
tp_itemcount AS [Total Item]
FROM lists inner join webs ON lists.tp_webid = webs.Id
WHERE tp_servertemplate = 107
ORDER BY tp_itemcount DESC
--------------------------------------------------------------------------------------------
-- Liste du nombre total d'éléments pour les bibiliothèques de formes InfoPath
--------------------------------------------------------------------------------------------
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,
tp_itemcount AS [Total Item]
FROM lists inner join webs ON lists.tp_webid = webs.Id
WHERE tp_servertemplate = 115
ORDER BY tp_itemcount DESC
--------------------------------------------------------------------------------------------
- Liste du nombre total d'éléments pour les listes génériques
--------------------------------------------------------------------------------------------
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,
tp_itemcount AS [Total Item]
FROM lists inner join webs ON lists.tp_webid = webs.Id
WHERE tp_servertemplate = 100
ORDER BY tp_itemcount DESC
--------------------------------------------------------------------------------------------
-- Nombre total de documents
--------------------------------------------------------------------------------------------
SELECT COUNT(*)
FROM AllDocs
INNER JOIN Webs On AllDocs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE AllDocs.Type <> 1
AND RIGHT(LeafName,4) NOT IN ('.stp','.dwp','.xfp','.dwp','.inf','.ccs','.xml')
AND RIGHT(LeafName,5) NOT IN ('.aspx')
AND (LeafName NOT LIKE '%template%')
--------------------------------------------------------------------------------------------
-- Total de documents MS Word
--------------------------------------------------------------------------------------------
SELECT count(*)
FROM AllDocs
INNER JOIN Webs On AllDocs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE AllDocs.Type <> 1
AND ((LeafName LIKE '%.doc') OR (LeafName LIKE '%.docx'))
AND (LeafName NOT LIKE '%template%')
--------------------------------------------------------------------------------------------
-- Total de documents MS Excel
--------------------------------------------------------------------------------------------
SELECT count(*)
FROM AllDocs
INNER JOIN Webs On AllDocs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE AllDocs.Type <> 1
AND ((LeafName LIKE '%.xls') OR (LeafName LIKE '%.xlsx'))
AND (LeafName NOT LIKE '%template%')
--------------------------------------------------------------------------------------------
-- Total de documents MS PowerPoint
--------------------------------------------------------------------------------------------
SELECT count(*)
FROM AllDocs
INNER JOIN Webs On AllDocs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE AllDocs.Type <> 1
AND (RIGHT(LeafName,4) IN ('.ppt','.pps', '.ppm')
OR RIGHT(LeafName,5) IN ('.pptx','.ppts','.pptm'))
AND (LeafName NOT LIKE '%template%')
--------------------------------------------------------------------------------------------
-- Total de documents TXT :
--------------------------------------------------------------------------------------------
SELECT count(*)
FROM AllDocs
INNER JOIN Webs On AllDocs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE AllDocs.Type <> 1
AND (LeafName LIKE '%.txt')
AND (LeafName NOT LIKE '%template%')
--------------------------------------------------------------------------------------------
-- Total de fichiers compressés
--------------------------------------------------------------------------------------------
SELECT count(*)
FROM AllDocs
INNER JOIN Webs On AllDocs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE AllDocs.Type <> 1
AND ((LeafName LIKE '%.zip') OR (LeafName LIKE '%.rar') OR (LeafName LIKE '%.7z'))
AND (LeafName NOT LIKE '%template%')
--------------------------------------------------------------------------------------------
-- Total de documents PDF
--------------------------------------------------------------------------------------------
SELECT count(*)
FROM AllDocs
INNER JOIN Webs On AllDocs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE AllDocs.Type <> 1
AND (LeafName LIKE '%.pdf')
AND (LeafName NOT LIKE '%template%')
--------------------------------------------------------------------------------------------
-- Total de fichiers JPG
--------------------------------------------------------------------------------------------
SELECT count(*)
FROM AllDocs
INNER JOIN Webs On AllDocs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE AllDocs.Type <> 1
AND ((LeafName LIKE '%.jpg') OR (LeafName LIKE '%.jpeg'))
AND (LeafName NOT LIKE '%template%')
--------------------------------------------------------------------------------------------
-- Total de fichiers GIF
--------------------------------------------------------------------------------------------
SELECT count(*)
FROM AllDocs
INNER JOIN Webs On AllDocs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE AllDocs.Type <> 1
AND (LeafName LIKE '%.gif')
AND (LeafName NOT LIKE '%template%')
--------------------------------------------------------------------------------------------
-- Total de fichiers autres que DOC, DOCX, PDF, XLS, XLXS, PPT, PPM, PPS, PPTX, PPTM, PPTS,
TXT, Zip, RAR, 7z, ASPX, DEWP, STP, CSS, JPG, JPEG, GIF, RTF,
TIF, TIFF, WAV, PNG, MP3, MP4, AVI, MPG, MPEG
--------------------------------------------------------------------------------------------
SELECT COUNT(*)
FROM AllDocs
INNER JOIN Webs On AllDocs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE AllDocs.Type <> 1
AND RIGHT(LeafName,4) NOT IN ('.doc','.xls','.txt','.zip','.rar','.jpg','.gif','.pdf',
'.ppt','.pps', '.ppm','.png','.tif','.swf','.wav','.rtf',
'.mp3','.mp4','.avi','wvm')
AND RIGHT(LeafName,5) NOT IN ('.docx','.xlsx','.jpeg','.pptx','.ppts','.pptm','.mpeg')
AND (LeafName <>'_webpartpage.htm')
--------------------------------------------------------------------------------------------
-- Taille totale de tous les documents
--------------------------------------------------------------------------------------------
SELECT SUM(CAST((CAST(Size AS DECIMAL(12,2))/1024000) AS DECIMAL(12,2) ) ) AS 'Total Size in MB'
FROM AllDocs
INNER JOIN Webs On AllDocs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE ALlDocs.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')
|
|
|
|
|
Hi - I'm looking to get a query (or queries, I guess) to display workflow history on documents for when they were changed and by whom, etc. Any suggestions are appreciated - Thanks.
|
|
|
|
|
Hi,
nice Article! Do you have a idea, i need to make a clone of a ChildSite under the root,
is this posible todo this with sql ?
Task: Copy of ChildSite
http://ww2/mainsite/ch1 to
http://ww2/mainsite/ch2 with ALL content (clone) new Site have a different name
can i do this only with sql states?
sincerely lars
larspl
|
|
|
|
|
Just what I was looking for... great Job!!!
|
|
|
|
|
Hi--Thanks for your article. Can you provide a couple of user queries for wss v2? 1) Return the same info as for site hierarchy but include all the admins for each site; 2) email address to which access requests go by site. Many thanks!
|
|
|
|
|
Hi,
When i run the query in subject i receive the following error:
--Arithmetic overflow error converting numeric to data type numeric.
--Warning: Null value is eliminated by an aggregate or other SET operation.
Any idea what this might be? I am able to run it on a small SPS site, but when i try this on a large site it gives me that error.
Thanks for all those scripts by the way, they were very helpful to me so far
|
|
|
|
|
My aim is to pick most active pages
pdfr
|
|
|
|
|
Can jou make a query to show alle the site a user is member of
>Hansen from DK
|
|
|
|
|
Excellent set of queries for SP Developers...
I think it would lot of people if you please put these queries in a TEXT file for ease of download.
When you copy all these queries into clipboard, you get HTML-embedded text.
Thanks,
HyperX.
|
|
|
|
|
this information has proved to be very useful! i'd like to see the second part of this...
I have demonstrated how easy it to analyze a SharePoint Portal solution. In the second part of the article, I will try to write queries on Users, User Profiles, Subscriber Lists, Site Groups, Cross Site Groups etc.
|
|
|
|
|
We have several managed paths and, while it was no biggie to update your queries to consider WSS paths other than 'sites', ti would be great if your queries took these into account automatically.
If I can figure out how to do that, I'll send you an update.
-Kevin Buchan
|
|
|
|
|
Thanx Ashaman, I will update my article and mention your name to give you credit.
Adnan
MCP, MCAD.NET, MCSD.NET
|
|
|
|
|
Hi,
The information provided through the article is excellent,but i knew that you direct queries to SBS is not recommended at all,because it breaks the SBS object model... if you want to query from SBS you have to use SBS web services... this is the information i got from MS Support.... then please update or hint this info in this article.
Moustafa M.Arafa
Senior Solution Developer,DotNet Trainer.
MCT,MCSD.Net,MCAD.Net,MCP
DotNet Technical Support Manager.
http://www.dotnetboom.net
|
|
|
|
|
Hi,
As long as you don't make any changes in the SharePoint Proprietry database schema, it is allowed.
Adnan
MCP, MCAD.NET, MCSD.NET
|
|
|
|
|
Very true! So far, you can "read" the databases, but no "writes" are allowed as this could void your support from MS. You have to use the SharePoint Object model instead to make any updates/deletes/inserts.
Thanks,
Shola Salako, MCSE
|
|
|
|
|
Not realy ! You may have some PB
http://support.microsoft.com/kb/841057/en-us
Additionally, customers must not make support-guided changes outside a support incident or provide such changes to other customers for use outside a support incident. In addition to the items previously mentioned, customers are strongly advised against direct access in a read-only manner to these databases unless Microsoft protocol documentation is followed exactly. Accessing these databases programmatically or manually could cause unexpected locking within Microsoft SQL Server that can result in overall performance problems. To view the SharePoint Products and Technologies protocol documentation, visit the following Microsoft Web site:
http://go.microsoft.com/fwlink/?LinkID=119903
EROL
|
|
|
|
|