SQL query to count files in subfolder in Sharepoint 2007 / MOSS

Hi,

Do you need this query just for a library?
Cause this may be easier and safer realized by creating a view that will count for you. Create new view, select to count for Name for example, and on Folder section choose to "Show all items without folders". Then you can navigate to the desired folder and choose this new created view and it will count for you the files within that folder.

Direct access to the SQL database violates the Microsoft EULA for Sharepoint.  If any members here help you violate the EULA they are violating the EE Membership Agreement.

Thanks for your suggestions!

The reason for me to have a look into the SQL database is that using standard SharePoint functionality like creating a 'ViewWithCount' and using the 'Open with Windows Explorer' feature and right-clicking on a folder using 'Properties' to count all files gives a contradicting result. 'ViewWithCount' method gives me approximately 20000 files, and the other - 'Properties' - gives me 48000 files as a result. The counting result should be equal, so which count do I trust? Therefore I need to peek directly into the database. I am absolutely not going to access the files directly from the database. I need to count the number of active, not deleted, documents in the database, and I want to make sure I don't count any .aspx-files or similar default SharePoint files.

Here is my effort so far on the query...

select DirName, LeafName, Type from AllDocs Where Type=0 and DirName like '%myfolder1/myfolder2%'

...Seems there is a 'DeleteTransactionId' column that i reckon is a flag for deleted, but not purged, items, and I do not want to include deleted items in my count.

I would really appreciate some further help or guidance on what I have to consider?

This is a huge difference. And a huge library for that matter.

Is the library indeed reporting more than 48000 items?
Do all files have a value in the field that you use for counting in view? Cause, it may be that you are counting on a field that is empty for some of them, and that is why you see such a difference. Try using another one.
So far, I would trust the numbers from Properties.

(you probably know, but just as reminder, a large library would have a big impact on search)

Thanks for the reminder, I'm aware of it.

The total document count in the Document Library is 52888, but I'm interested in a subfolders file count, including nested folders within that subfolder.

I'm using the 'Name' field for counting in the view, and all files has a 'Name'  value.

I'm leaning towards that the 48000 count from Properties is wrong because when i do a 'select * from AllDocs Where DirName like '%myfolder1/myfolder2%' query I get a count of approx 29000 items. And I can see that some of those items are plain folders, and I don't want folders included in the count of files.

But I need to be able to count exactly every business document since the files are under GxP regulations and I have to keep track of every one of them when I'm soon gonna export files out of SharePoint to a file share.

As for the situation for now, I don't know what file count number from SharePoint to trust... but I know I can trust what is in the content database. I'm using SQL Server Management Studio.

So... What is the SQL query to count/select files (active business documents only, not the default SharePoint application files like aspx or deleted files still in the recycle bin)  based on DirName directly in a content db?

What is the number reported from the SharePoint Designer?
Designer is supported and you can use the same right click->properties to determine the number of files in a folder.

@tedbilly - is it still a violation of the EULAs if an administrator runs a select query in a development/qa environment?

Yes.

So how can I solve the problem then?
Do I have to make a query through the Object Model, or do I have to post that as a new question?

Using the Web Services takes it to a different level.
Are there any well known third-party or open source solutions that can do this?

Not that I'm aware of.  Bamboo Solutions, Quest Software, Avepoint and Telerik are candidates to check.  Look at CodePlex as well in the Sharepoint area.  There are a lot of solutions from Microsoft that they didn't turn into commercial solutions.

Also. www.wssdemo.com has a LOT of information on Sharepoint including vendors et cetera.

Sharepoint 2007 3rd party development didn't take off as much as 2010 will.  Developing custom deployment solutions in 2007 was a little tricky but in 2010 it's trivial.  You can use Visual Studio 2010 to do WSYIWIG customization of Sharepoint.

If you search the web some people use CAML queries from SSIS to pull data from Sharepoint as well but either the API or web services is the most common approach.

I haven't found anything yet.
Think I'm gonna try to code something.

Well then my comment http:#33016330 is the correct answer

Actually, I contacted microsoft support and they told we didnt have the most up to date sp and patches, so that is likely to be nexr step, but some bosses  in company have to agree first.

Applying patches or updates have nothing to do with enumerating a document library.  They won't provide a solution for this question.

Hi.

Sorry for not replied, have been away on vacation with my kids. The reason for me not accepting a solution is simply that suggested solutions have not solved the issue for me. Suggestion #33016330 didn't work on my MOSS server, therefore I turned to Microsoft. When talking to Microsoft support they confirmed the issue and stated it should be solved with installing the latest service pack, but as that requires a revalidation of the system that decision is still up to my bosses so I cannot confirm MS solution is actually working - yet.

And talking to MS support it was also OK to make sql select statements to the database. And by that I could see that the count corresponded with the 'ViewWithCount' number. So in reality I guess that the select statement part of the solution, but we still have to validate that statement thoroughlly also - i'm no SQL expert. And while we're going to export several folders it all should be best to do counting through the UI, therefore I'm hoping for bosses to approve for installing the service pack.

I'm most grateful to the experts participation here driving the actions leading towards contact with MS, thank you!

I'm sorry but that MS Support person is mistaken.  The front line support are contractors and are not MS employees.  I've had higher level support that were closer to the MS group directly contradict information provided by the first level of support who do not always get their stories straight.

There might be a bug with the API that is reporting an incorrect number for the 'ViewWithCount' but that was NOT your original question.

Thanks for insight on first line support. I don't know exactly what level we were at. We reported the issue as business critical and the guy sounded pretty experienced, but I really cannot say...

The support person requested additional comment from his supervisor when we talked. He could not guarantee the select statements quality as he either was no sql expert, but assured that the weird properties behavior was a known issue and that sp3 would solve it.

Regarding the supported state of the database; the DB is my company's property, and keeping the overall system GxP definitely has higher priority than keeping the DB in a supported state for Microsoft, cause we're going to shut it down anyway. The truth is in the DB... what if my bosses decide that it is a higher priority to get a straight answer out of the DB through a select query then keep it in a supported state?

Well as I said earlier according to EE policy I can't help you violate a EULA.  If we met in a pub and you asked me the same question you might get a different answer. ;)

Ok, to bad.

Current update on this is I'm still waiting for bosses to decide on sp3 or not. Meanwhile I'll got the code from comment  http:#33016330  to work and to correspond with the count from comment  ID: 33002163 . Together those two different methods of counts may be sufficent, that is also to be decided by bosses. What would we do without all those bosses! ;)
Thanks