|
--------------------------------------------------------------------------------------------
-- 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')
|
|
|
|