MC+A

Replacing SharePoint search with Google search is a no brainer! SharePoint’s built in search is finicky and unreliable. Not having to worry about you Intranet’s search failing and having the ease of use that come with Google search will make you understand why the appliance is worth it. How do you know which appliance is right for your SharePoint and other systems? A good approach is to know how much content you are going to be indexing. The Google Mini has a document limit of 100,000 documents, the Google Search Appliance starts at 500,000 and goes up to a billion when you chain several GSAs together! So many people are curious to know if there is a way to get a page count or a means to account for all the content on a SharePoint site. I hope some of these SQL queries help you find the answers you are looking for.

Here are some SQL Queries to find out the total number of documents/files:

1. Total number of documents:

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

2. Total MS Word documents:

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

3. Total MS Excel documents:

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

4. Total MS PowerPoint documents:

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

5. Total TXT documents:

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