1

I have a couple of questions:

  1. Where does SharePoint list stores data in SQL?
  2. Is there article for SP2013 on MSDN or TechNet? - I can find outdated articles which explain table structure for a list but those articles are not maintained anymore by Microsoft hence this question.
  3. Can I access data directly and is it recommended?

I found SharePoint List Query Designer and Text-based Query Designer User Interface which are providing an option to query a list like a table. Has anyone explored these tools?

4

Where does SharePoint list stores data in SQL?

In SharePoint Content DataBase at AllLists INNER JOIN AllUserData ON AllLists.tp_ID = AllUserData.tp_ListId where the data stored at AllUserData.tp_ColumnSet column as XML


Can I access data directly and is it recommended?

No, it's not recommended and not supported and I advise you to don't touch the SharePoint database to don't lose the Microsoft support and to avoid DB locks

unsupported database changes include, but are not limited to, the following:

  • Adding database triggers
  • Adding new indexes or changing existing indexes within tables
  • Adding, changing, or deleting any primary or foreign key relationships
  • Changing or deleting existing stored procedures
  • Calling existing stored procedures directly, except as described in the SharePoint Protocols documentation
  • Adding new stored procedures
  • Adding, changing, or deleting any data in any table of any of the databases for the products that are listed in the "Applies to" section
  • Adding, changing, or deleting any columns in any table of any of the databases for the products that are listed in the "Applies to" section
  • Making any modification to the database schema
  • Adding tables to any of the databases for the products that are listed in the "Applies to" section Changing the database collation
  • Running DBCC_CHECKDB WITH REPAIR_ALLOW_DATA_LOSS (However, running DBCC_CHECKDB WITH REPAIR_FAST and REPAIR_REBUILD is supported, as these commands only update the indexes of the associated database.)
  • Enabling SQL Server change data capture (CDC)
  • Enabling SQL Server transactional replication
  • Enabling SQL Server merge replication

Supported database modifications

  • Exceptions to the prohibition against database modifications are made for specific usage scenarios:
  • Operations that are initiated from the SharePoint administrative user interface
  • SharePoint specific tools and utilities that are provided directly by Microsoft (for example, Ststadm.exe)
  • Changes that are made programmatically through the SharePoint Object Model and that are in compliance with the SharePoint SDK documentation
  • Activities that are in compliance with the SharePoint Protocols documentation

For more details Support for changes to the databases that are used by Office server products and by Windows SharePoint Services


Regarding SharePoint List Query Designer and Text-based Query Designer User Interface which are providing an option to query a list like table. Has anyone explored these tools?,

It's supported tools, Because it doesn't retrieve the data from SQL content Database directly

  • 1
    very nice, useful :) – user37251 Sep 18 '17 at 19:13
  • Damn! I found this blog but ignored to read. – vikbehal Sep 18 '17 at 19:50
  • Have you tried List Query Designer? – vikbehal Sep 18 '17 at 19:51
  • Yes, in reporting service – Mohamed El-Qassas MVP Sep 18 '17 at 20:21
  • So, If I were to query data from list using List Query Designer, the output is like we get from SQL? Can we export it? – vikbehal Sep 18 '17 at 20:24
1

All list data is stored in AllUserData; the specific user enterable values are stored in the tp_ColumnSet field. More information can be found at AllUserData Table, part of the Protocol documentation. This document is current.

It is not supported to query these tables directly as you may cause unintended locks.

  • Damn! I found this blog but ignored to read. – vikbehal Sep 18 '17 at 19:50

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.