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

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.html

Write 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:



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.