I want to know if below proprieties are enabled or disabled for any SharePoint 2013 List/Library. How we can get using SQL Query from ContentDB (AllLists Table), which column I have to use in my query?

  • EnableVersioning
  • UniqueItemPermissionsEnabled

As we know if we want the result of lists using SQL query which are not deleted from site we can use below query:

SELECT * FROM AllLists  WHERE tp_DeleteTransactionId = 0x

Similar way I want the result set using SQL query where above two properties are enabled or disabled

Thanks, Viren


As this is part of the SharePoint list schema, it is compressed in the tp_Fields column. Unfortunately there is no public documentation as to how to decompress this value into something you can use. And as Thriggle points out, you should be using the SharePoint OM. If you're dead-set on making SELECT queries, always use WITH (NoLock).

  • Trevor, Thanks for the reply. I have decompressed the tp_fields but it's showing only information related to Fields and Field types which are attached to the list. – Virendra Kumar Oct 7 '15 at 15:44

You should not run your own SQL queries against the SharePoint database for multiple reasons. According to Microsoft:

Direct modification of the SharePoint database or its data is not recommended because it puts the environment in an unsupported state.

If a server component requires information from the database, it must get that data by using the appropriate items in the SharePoint object model, and not by trying to get the items from the data structures in the database through some query mechanism.

Instead of using a SQL query to retrieve the data you want, you should use the SharePoint object model, either from one of your web front end servers using Powershell or .NET code (server side), or from another computer using the client object model (such as the available web services and REST services, or the .NET or JavaScript client object models).

A Powershell script run from a web front end server is probably the simplest option for a one-time report. I recommend asking a new question if you need help with the syntax or specifics of running a Powershell script to query SharePoint.

  • Thanks for the information. Yes I am aware of that we have to use SSOM/CSOM for these kind of information. But I am developing a tool where I have to get all faulty features, list before migration (Also after retraction of the custom features). What are the custom components have been used in Farm/WebApp and what properties are enabled for those components. I already got the information using SSOM but it's taking too much time to generate the reports for big Farm/WebApp. That's why I am looking for SQL query help. – Virendra Kumar Oct 7 '15 at 6:26

We have to add below SQL conditions to get the EnableVersioning and UniqueItemPermissionsEnabled values using SQL Query from Content DB

WHEN (tp_Flags & 0x0000000000000080) > 0 THEN 'TRUE' 
END AS EnableVersioning,  

WHEN tp_HasFGP = 1 THEN 'TRUE'  
END AS UniqueItemPermissionsEnabled, 

Extra Information

DeleteVersions ||> 0x0000000000000080 ||> Allow deletion of past versions of a list item or document. ^Link^

0x0000000000000080 ||> This list has versioning enabled, and supports creating historical versions of list items when changes occur. This bit MUST be ignored for Lists with a List Base Type of survey. ^Link^

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.