Generated Sql views based on SharePoint Content


I think that every SharePoint developer looked into the SharePoint content database to see how all data is stored in sql tables. In case you didn’t, look at it and be surprised 🙂 All SharePoint content is placed in one big table.

All List Fields are translated in sql columns with no-meaning-names like “varchar1, varchar2, varchar3, float1, float2…). So if you want to write an sql to query to query all items from a list, you need to first do some field-translation which can be very time consuming and can causes some serious headaches 😉

In all my years as SharePoint developer / consultant, customers asked me: can we do some reporting based on SharePoint data?? answer: not easy because of the unstructured way that SharePoint saves data in sql …

Within the last couple of days I started to write a console application which generates a lot of Sql Views, depending on your site topology, based on Content Types and Lists.

Example:
Listname: “Orders”
ContentTypes enabled?: “Yes”
Extra Fields: “Status”,”Price”

The view thats been generated for this will look like this:

CREATE VIEW vwCtp_Order AS
 SELECT
  tp_ID as [ID],
  tp_ListID as [ListID],
  tp_Version as [Version],
  tp_Modified as [Modified],
  tp_Created as [Created],
  nvarchar1 as [Title],
  float1 as [Price],
  nvarchar21 as [Status]
 FROM [SPDBServer].[WSS_Content].dbo.AllUserData
 WHERE tp_ContentType = ‘Order’
GO

Reporting Purposes
This is very usefull for reporting, because now you can query this content easier:

SELECT Title, Status, Price
FROM vwCtp_Order
WHERE Status = ‘Final’

Last but not least:
I’m gonna probably put this tool on codeplex. More info will follow.

Advertisements
This entry was posted in Geen categorie and tagged . Bookmark the permalink.

2 Responses to Generated Sql views based on SharePoint Content

  1. Aaron says:

    How did you make out with this? I’d be interested to know if you continued to work in this

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s