1

I´m searching for informations about the SharePoint 2010 Database.

Are there infomations about the different tables/views etc.? I don´t found any documents about the SharePoint 2010 Databases on msdn etc. only for 2003 and 2007.

|improve this question
  • What's your goal for accessing the database? You should be able to do everything through existing SP tools and resources. – David Lozzi Dec 1 '11 at 14:36
  • You should only need to check the databases if something is seriously wrong. – Elio Struyf Dec 1 '11 at 15:29
  • I'm +1'ing this thing because there's nothing about accessing the databases in the FAQ and because it is an extremely common question. – shufler Dec 1 '11 at 19:01
  • 1
    To gain further personal knowledge about how the platform works and database design in general, I see nothing wrong with learning how the database works - as long as you know that changing ANYTHING renders your environment unsupportable. By anyone. – James Love Dec 1 '11 at 20:32
  • Thanks for your comments! I´m studying Computer Science and working part-time at a IT company. At the moment I´m searching for an way to generate different reports from the environment. A colleague tells me, that maybe I can found useful informations in the WSS-Logging Database. So I start searching for informations about SharePoint database schema and I found nothing! Thats the reason for this question! By the way... I don´t want to change/direct access the production database! – LaPhi Dec 1 '11 at 22:37
3

All queries against SharePoint data should be done through the SharePoint Object Model or Web Services. Creating solutions that access the database directly will result in an unsupported installation of SharePoint.

Though you have nothing preventing you from doing direct SQL queries, you will not get any support on this site for doing anything that will make your farm unsupportable by Microsoft.

|improve this answer
  • 3
    Answers like this are exactly what I DON'T expect on StackExchange. Save the tow-the-microsoft-line replies for the useless MS support forums. There's plenty you can use the database for that will not not at all affect the integrity of the system, e.g. SELECT queries. Want to know how many word docs are on a site? How many folders? How many gigabytes of files in each folder? These queries are SUPER easy to run against the DB. – C.List Jan 27 '16 at 16:30
3

You'll likely want the SharePoint Products and Technologies Protocol Documentation. These contain descriptions about the database structure, including table schema, sprocs, and so forth... Along with a lot of other information. And as everyone else has noted, making changes is, for the most part, unsupported. Check out KB841057 for more information on that.

|improve this answer
1

Maybe you can start from here: http://msdn.microsoft.com/en-us/library/dd587562(v=office.11).aspx

But let me remind you, modifying the tables directly is risky and is not supported.

|improve this answer
  • Thank you for your comment! But your articel is about the SharePoint 2003 Database Tables. I think it is not the same Database Schema in 2010 – LaPhi Dec 1 '11 at 13:50
  • 2
    AFAIK there is not a published document about SharePoint 2010 db structure and database has not changed much since 2007. Here is a more complete reference msdn.microsoft.com/en-us/library/dd358229(v=PROT.13).aspx – Élodie Petit Dec 1 '11 at 14:22
  • 2
    Actually, the table schemas change all the time. CUs are known to change table names, column names, stored proceedures, etc. Just a recent example: SP2010 SP1 changed the dbo.Sites table in the content database to dbo.AllSites. This is a primary reason why modifying the databases is unsupported, because you can't be sure your changes will conflict with a future update. – shufler Dec 1 '11 at 18:56
  • 1
    Sites still exists as a View for backward compatibility. – James Love Dec 1 '11 at 20:33
-1

Thread is 4 years old, and SP is fading away (partly, IMO, because the API is so abstruse and the design so closed), but thought I'd add a response...

If you understand the concepts of SharePoint, then it's pretty straightforward to guess what the tables do based on their names... It can be a little more difficult to determine exactly where specific pieces of information are stored. AllDocs are many to one on AllLists and AllSites. AllDocVersions are many to one on AllDocs. AllDocStreams is one-to-one with AllDocVersions and simply stores the raw data for the files. AllUserData stores metadata, etc... etc...

The following query will get pretty much all information on the CURRENT version of documents in the system:

select top 100 * 
from AllDocs ad WITH (NOLOCK)
    inner join AllDocStreams ds with(nolock) on ad.SiteId=ds.SiteId and ad.Id = ds.Id and ad.InternalVersion = ds.InternalVersion 
    inner join AllUserData as U WITH (NOLOCK) on ad.Id = U.tp_DocId and u.tp_iscurrent=1

I'm not totally sure where non-document list items are stored, perhaps someone else can say.

It might be easier for you to ask for where to find a specific piece of information.

Cheers, CList

p.s. I'm not too sure why people get so worked up about the "unsupported" nature of working with the database directly... I mean obviously it's unsupported, and so you need to test and possibly change your code before/after you upgrade sharepoint - big deal! If you can can figure out how to do something 1000 times faster by reading or even updating the SP database directly, and that has real business value for you, then you're probably a big enough boy/girl to understand the trade-off for it being unsupported.... It could very well be the case that the thing you build will work well for the next 8 years and then your company (or your client) will move to a completely different platform without ever caring to upgrade - or even patch - SP in the meantime. You may roll your eyes, but I've seen this happen quite often - especially in very customized SP deployments where even supported mods need to be re-coded before upgrading.

|improve this answer
  • BTW, one of my clients (SP2010) has a 100GB content database that they want re-deployed to dev and test every 2 months. They don't need all of the doc content there - but they want the doc metadata. I sync records by simply deleting rows from dev and test DBs directly, insert missing document records using the API and a compare query on the production DB for missing docs in dev and test and inserting dummy placeholder docs that are tiny pdfs, word docs, etc, and then I run another query to update the metadata via DB update. Been working great for years, and saves loads of time and storage. – C.List Jan 28 '16 at 13:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.