From: http://developer.ebusiness-apps.com/ebakb/ebakb.asp?artid=174&catid=28
So how does SharePoint store the data for the lists? It turns out that for each site there is a seperate SQL database, containing a number of tables. The two important tables for us are the Lists and the UserData tables. The Lists table contains an entry for each list within the site (and its subwebs) and there is a column called tp_Fields that contains an XML fragment describing the schema for the list. In this schema, each field in the list is associated with a column in the UserData table. The UserData table contains a column for every possible field that can exist in a list. For example, there are 32 floating point columns, 16 datetime columns, etc. The schema in tp_Fields then associates each field in the list with a column in the UserData table. All of the data from every single list in the entire site is then kept in this UserData table.
So it is not a trivial task to write a SQL query that will retrieve all of the data for a specific SharePoint list. First we have to determine which columns in the UserData table correspond to which fields in the list. This requires getting the schema for the list from the Lists table and working out the mappings. Then we can retrieve the proper data from the UserData table, and perform our reporting on that data. To ease this process, I've created an application that goes through the Lists table and creates a seperate SQL View for each SharePoint list. Then we can perform our queries on the View for the list, and it will be as if we are accessing the list itself.
Niciun comentariu:
Trimiteți un comentariu