Finding SharePoint lookup list values in the database

0
votes

This is a repost, but I'm trying to articulate my question a little better.

I am trying to find out where data for SharePoint List Fields are stored in the Content Database when the fields are lookup values. Like in this post: https://stackguides.com/questions/9137557/sharepoint-list-lookup-values-in-the-database I know that you're supposed to read the XML and find the correct column in the AlUserData column. That works fine when the data is a "Single-line-of-text". What I see, though, is that if it's a lookup value, the column referenced in the XML will be null. Does anyone know why this is, or how I can find the lookup values?

1
sharepointsharepoint-2010
what are you trying to achieve? – Eric Herlitz
I'm trying to extract some metadata about files straight from the DB. So, for example, I have 50,000 files with a background color set in their content type, and the bG color is a multiselect value that uses a list. I want to get this data and drop it in, say, a spreadsheet, so that I can have a list with the filename and it's background color (and other stuff). I know I can do this through the API, but for the high number of files, it takes too long to run the job. I'm guessing SQL will perform better. ... Thanks! – Josh C
Also, I'm not building this as part of an app design. It's a script I'm going to run a lot over the course of a week, but I wouldn't ship anything that relied on access to the sharepoint DB. – Josh C

1 Answers

0
votes

(1)You can select data directly from content database, from AllUserData table. For build correct sql query i can suggest use "SPUD" utility. How to use:

  1. open it, enter sharepoint url
  2. select list to query
  3. right click -> generate list sql
  4. copy and execute in Sql management studio

In query results you can find column value.

(2)But i suggest to use Sharepoint Object model, with some optimization it works quick up to 500K items.

(3)Try use "Linq to sharepoint" to perform big query to list by one CAML request