0

First off, I'd like to say that I understand the limitations and the risk of accessing a Sharepoint Database directly. I am not updating the SP Content, just simply querying the data in a SQL View for another application.

Generally, when I need to create a view for SP data I have had to manually map the List/Library fields to their dynamic database columns. However, this can get fairly tedious depending on the size of the list. For example:

  • nvarchar1 = 'First Name'
  • nvarchar2 = 'Last Name'
  • nvarchar3 = 'Phone Number'

As Sharepoints content is stored dynamically, I figure that there has to be a table which stores these mappings.

Where are the above mappings stored in the content database, and how can I query it so I can dynamically pull data from the AllUserData table for a specific list?

  • I would try using SQL Profiler and see what statements gets executed on adding a new field from Browser. – Amal Hashim Jun 4 '15 at 16:52
  • 1
    Even still, I would not recommend it, there are a slew of other tools to get the data out than direct query access. – Eric Alexander Jun 4 '15 at 17:30
  • Possibe duplicate of sharepoint.stackexchange.com/questions/18314/… ? – Eric Alexander Jun 4 '15 at 18:10
  • @Eric Alexander - How would you recommend I query this data in SSRS? I can easily hook up a Sharepoint List Datasource, but since I deal with 100's of different templated lists, I need that List Datasource to be dynamic based on the Site Name. – Lando Jun 4 '15 at 18:43
3

You can give it a try using below sql query.

DECLARE @listId AS UNIQUEIDENTIFIER = 'LISTGUID';
SELECT DISTINCT
     tp_Fields AS 'Fields'                                         
FROM   AllLists
WHERE  tp_ID = @listId

If you are using C# code to get the details you can use the following methods.

public static string GetFieldXmlString(string connectionString, Guid listId)
{
    string fieldXml = string.Empty;     
    var cmd = [Select command mentioned above]
    var data = new DataTable();

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (SqlDataAdapter da = new SqlDataAdapter(cmd, conn))
        {
            da.SelectCommand.CommandTimeout = 0;
            da.Fill(data);
        }
    }
    fieldXml = getXml((byte[])data.Rows[0]["Fields"]);
    return fieldXml;
}

private static string getXml(byte[] tpFields)
{
    using (var memoryStream = new MemoryStream(tpFields))
    {
        // ignore the first 14 bytes; I'm not sure why but it works!
        for (var index = 0; index <= 13; index++)
            memoryStream.ReadByte();

        var deflateStream = new DeflateStream(memoryStream, CompressionMode.Decompress);

        using (var destination = new MemoryStream())
        {
            deflateStream.CopyTo(destination);

            var streamReader = new StreamReader(destination);
            destination.Position = 0;
            return streamReader.ReadToEnd();
        }
    }
}

Hope this helps to resolve your query.!!

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.