Hi All,
Here we are with Run SQL Queries and Fetch List Data from SharePoint Database (WSS_Content) Part-2
For those who are want a quick refrence of how to query Sharepoint content database
View Part-1 :
Problem Statement:I have a List
“Company Information” I have a List
“Contact”The relation between them is
“Company Information” List Item ID is Foreign Key in “Contacts” List.
I want to Show a web Part with data like:
Contact – x Belongs to Company – x
Relate the Problem and the Display of result with the above screen shots
Solution with SharePoint Object Model:Fetch Each List Item from List “Company Information”
Get the Company ID
Fetch List Item from List “Contacts” where “Company ID” is present.
Append this information in a Data Grid and Display.
In short you will have to Loop throught the List,Compare and Display.
Solution with SQL Query on SharePoint internal Content Data base:There are Two Lists in SharePoint internal Content Data Base
1) Lists 2) UserData
We have explained about this in Part -1.
For More information refer to this Post.
http://www.sharepointkings.com/2008/07/run-sql-queries-and-fetch-list-data.htmlWrite a SQL Select Query.
Run it and test it in as a New Quey in SQL Query Pan.
Take a Data Grid and Bind it with the SQL command.
Simple isn’t it !!!!
Core Logic:Now how do you get it?
The TABLE: UserData stores all the Data (Contents of any List).
When you will query the Wss_Content data base
With a query like
Select * from UserData;
You will initially go mad. But don’t freak out. Try to understand the relation ship.
When you want to get the data from multiple Lists,
You will have to apply a
Self Join to the TABLE :
UserData.
To understand more do a practical.
Create Two Lists as you see in the screen short
Copy the following queires and Run it on your Sharepoint wss_content database.
In this case the Sharepoint wss_content database is : WSS_Content_11112
use WSS_Content_11112
select nvarchar1,nvarchar3,nvarchar4 from UserData where tp_ListID in (
select tp_ID from Lists where tp_Title = 'Company Information');
select int1 as 'Company ID',nvarchar1,nvarchar3 from UserData where tp_ListID in (
select tp_ID from Lists where tp_Title = 'Contacts');
select CI.nvarchar1 as 'Company',C.nvarchar1 as 'First Name',C.nvarchar3 as 'Last Name'
from UserData CI, UserData C
where CI.tp_ListID in (
select tp_ID from Lists where tp_Title = 'Company Information')
and C.tp_ListID in (
select tp_ID from Lists where tp_Title = 'Contacts')
and CI.tp_id = C.int1
Refer below SQL Query Screep short:
DisclaimerRunning 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.
.JPG)
