Thursday, July 3, 2008

Run SQL Queries and Fetch List Data from SharePoint Database (WSS_Content) Part-1

Hi All,

Today is one of my happiest days with Sharepoint....Why??? read further....
We had a requirement where in we were required to prepare Lists with dependencies on each other.
This architecture resembled the traditional Normalized structure of Data Base.

Now for some reports we needed to extract data from multiple Lists based on these dependencies.
I wondered whether there was a way to extract data from the Sharepoint wss_content database?
I deep dived into it and found myself stuck as in sharepoint DataBase.
No 3NF form of normalization.

• Foreign keys (if any…) are never given the same column name as the primary key
• Encrypted Column Name
for instance tpID in one table (as a pk) can be tpListID in another table (as the foreign key)
• I personally find myself in a pool of doubts regarding referential integrity


But Guys…Don’t Feel Low….I have something for you.

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.

Have a look at this SQL Query Below:

select tp_ID,nvarchar1,nvarchar2,nvarchar3,nvarchar4 from UserData where tp_ListID in (
select tp_ID from Lists where tp_title like 'MyList-1');


So Query the Sharepoint DataBase Bindasssss!!!!!!
View Part-2





Disclaimer
Running Direct queries on Sharepoint Database is not recommended by Microsoft.
Simply it's not for the faint of heart. Directly querying or modifying the database can place extra load on a server, or can expose information to users in a way that violates security policies or personal information management policies.
I highly recommend doing all testing in a non-production environment.

2 comments:

kemperflow said...

I am doing this, but the deleted items in my list are still showing in the table. There seems to be a column called Is Current, but that isn't False. I have poured over the structure and I can't tell how to weed out the old records, although they definitely don't appear in Sharepoint anymore.

SharePoint Kings said...

there must be some deleted flag in table or may be some table for deleted item so sharepoint will join that table while fetching on front end.

so this is may be comment we do not have idea for deleted items.

will try to find and get back to you, if time persist.

data should be there as data will be in recycle bin so try to find Stored procedure for recycle bin and check how it populate data so may be you will get your KEY column.

but please avoid this method to fetch data. use object model either.




Share your SharePoint Experiences with us...
As good as the SharePointKings is, we want to make it even better. One of our most valuable sources of input for our Blog Posts comes from ever enthusiastic Visitors/Readers. We welcome every Visitor/Reader to contribute their experiences with SharePoint. It may be in the form of a code stub, snippet, any tips and trick or any crazy thing you have tried with SharePoint.
Send your Articles to sharepointkings@gmail.com with your Profile Summary. We will Post them. The idea is to act as a bridge between you Readers!!!

If anyone would like to have their advertisement posted on this blog, please send us the requirement details to sharepointkings@gmail.com