How to pull Sharepoint list data from SQL Server

If anyone has dealt with Sharepoint they know how difficult it can be to get custom web controls, data extraction, and general programming completed in this monster.  There is a great thing about Sharepoint in that its back end is SQL Server.  But along with the pro comes a con, obtaining that data is very painful.  Why?  Well to be quite honest I don't think the job that Microsoft did with creating the Sharepoint database was all that great.  Reasons being:

• foreign keys (if any…) are never given the same column name as the primary key
• column names being cryptic
• for instance tpID in one table (as a pk) can be tpListID in another table (as the foreign key)
• referential integrity is used in some areas and forgotten??? in other tables
• the use of NULLS which can return disastorous results if you are not careful is misused beyond belief
• and whatever happened to 3NF (check out the table UserData for instance)?

Ok after reading that list most of you are thinking why on earth would I ever use such a tool, the answer is I have to, it’s Microsoft.  Anyhow as I was saying the nice thing is it’s got that SQL backend which means I can use that data to report on in Reporting Services.  Sharepoint is nice as a basic package, but companies always have specific business requirements which cannot all be embedded by MS in Sharepoint.  So one must program or at least change a business process to get that functionality in Sharepoint.
One important object used in Sharepoint is the List object.  Lists are much like your .NET data grids allowing users to enter data and fill a grid with relevant information.  The information in a list is stored in the UserData table (which is an odd name for the table), and the lists themselves are stored in the Lists table.  A list in the database has a GUID representation.  So say you need to create a report with the data inside your list one can query these two tables to get this data.  So here is a nice little example for you:
SELECT      dbo.UserData.tp_ID,
  dbo.UserData.tp_ListId,
  dbo.UserData.tp_Author,
  dbo.UserData.nvarchar1,
                dbo.UserData.nvarchar2,
  dbo.UserData.nvarchar3,
  dbo.UserData.nvarchar4,
  dbo.UserData.nvarchar5,
                dbo.UserData.nvarchar6,
  dbo.UserData.nvarchar7,
  dbo.UserData.nvarchar8,
  dbo.UserData.nvarchar9 ,
                dbo.UserData.nvarchar10,
  dbo.UserData.nvarchar11,
  dbo.UserData.nvarchar12,
  dbo.UserData.*                  –dont forget to modify this to snatch only the columns you need
FROM            dbo.Lists
INNER JOIN
                dbo.UserData ON dbo.Lists.tp_ID = dbo.UserData.tp_ListId
WHERE  
(dbo.UserData.tp_ListId = ‘{B44327F5-95E9-4504-A3BF-1E6751C452D6}’) 
–optional WHERE clause, this pulls data for ONE list
Notice my WHERE condition is simply pulling out one list’s data.  You can eliminiate this or use a parameter (with the actual list name as the selection to the end user).  You can also perform any SQL operation on this query since the data is simply sitting on SQL.  Then simply use this as your reports dataset, add some parameters and voila a Sharepoint report!