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:
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.
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.
Post a Comment