SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Anybody knows which is the name of the SharePoint table where the full URL of a web application is stored? for example "http://myserver:9090" Is in Config DB or inside the content DB. Thank you


This is the Join community webpart Join this community webpart

When you click on this button you become active member of the community. You can write in the newsfeed, make posts in the discussion lists etc. The problem is that I have a customized Webpart that must show all the communities where the user joined (inside Lists/Members/MembersAllItems.aspx) in a fast way.

If I fetch each web application, then all sites, and then check permissions for the current user, the process to get all the communities for this user would be extremely slow because we could have more than 500 communities. For that reason I was trying to create a Stored Procedure in a external DataBase to get the Titles and URL of the community.

This query works

SELECT distinct Webs.Title, Webs.SiteId, 
FROM AllUserData AS UserData 
            ON (UserData.[int1]=t2.[tp_ID]) AND 
                (UserData.[tp_RowOrdinal] = 0) AND 
                (t2.[tp_RowOrdinal] = 0) AND 
                ( (t2.tp_Level = 1) ) AND 
                (t2.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND 
                (t2.[tp_CalculatedVersion] = 0 ) AND 
                (t2.[tp_DeleteTransactionId] = 0x ) AND 
                (UserData.[tp_IsCurrentVersion] = CONVERT(bit,1) ) AND 
                (UserData.[tp_CalculatedVersion] = 0 ) AND 
                (UserData.[tp_DeleteTransactionId] = 0x ) 
    inner join Docs on UserData.tp_SiteId = Docs.siteId
    CROSS APPLY (SELECT TOP 1 deleted, Id FROM AllSites WHERE UserData.tp_SiteId = AllSites.Id) Sites
    CROSS APPLY (SELECT TOP 1 Title, SiteId, FullURL FROM AllWebs WHERE Sites.Id = AllWebs.SiteId) Webs
WHERE (UserData.tp_Level = 1) AND 
    (UserData.tp_RowOrdinal=0) AND ((UserData.[int2] = 1)) and
    t2.[nvarchar4] = '' 
        and Docs.LeafName = 'MembersAllItems.aspx'
        and Sites.deleted = 0

But here the problem is what you say. That is not supported by Microsoft to query directly the SharePoint databases. I am desperated!!

Thank you anyway

share|improve this question
Accessing anything directly in the databases is not supported in any way whatsoever by Microsoft. What is it you are trying to do and why are the numerous other ways of getting to that data not working for you? – Dave Wise May 9 '13 at 20:45
Because there isn't any way to get all the communities where the user joined. I have several web applications and I need to get the URL of these communities. I know that is not supported but I cannot find a faster way to achieve this. Do you know the name of the table? – Alvmad May 9 '13 at 20:52
i also have the same requirement ? how did you do this – Karan Mar 29 '14 at 11:10
Finally, I persuaded my client to take advantage of social capabiliteis of SharePoint 2013 so iterating throughout the SPSocialActor of type Site I get all the sites a user is following. Is it valid to you? Try to persuade him :) – Alvmad Mar 31 '14 at 9:11
I'd like to know the table name as welll... we have a permissions query that needs it. – PeterX Jun 10 '14 at 2:44

There is probably an easier way but here's a powershell script that should get you close without having to violate your support agreement with Microsoft.


Get-SPWebApplication | % {
    $webApp = $_
    $ctx = Get-SPServiceContext $webApp.sites[0]
    $upm = [Microsoft.Office.Server.UserProfiles.UserProfileManager]($ctx) 

    $obj=New-Object System.Object
    $obj | Add-Member -type NoteProperty -name WebAppUrl -value $webApp.Url
    $obj | Add-Member -type NoteProperty -name MySiteUrl -value $upm.MySiteHostUrl

share|improve this answer
Thank you for this solution. This is great to get the WA using powershell but please check my EDIT. Thank you again – Alvmad May 10 '13 at 8:41

Simply put: Don't ever query the SharePoint admin or content databases directly in SQL.

There are a number of other ways to get the web applications for the current farm.

1) PowerShell: Get-SPWebApplication

2) In C#:

var webApps = new Collection<SPWebApplication>();
if (SPWebService.AdministrationService.WebApplications != null)
    webApps.AddRange(SPWebService.AdministrationService.WebApplications.Where(p => p.IisSettings.Count > 0));
if (SPWebService.ContentService.WebApplications != null)
    webApps.AddRange(SPWebService.ContentService.WebApplications.Where(p => p.IisSettings.Count > 0));

The AdministrationService is where the Central Admin web app will be and the ContentService is where the rest will be.

Now, as far as your comment on getting communities, you have two ways to do this in 2013:

1) Use the SocialFollowingManager to retrieve all the sites the user is following. See

2) Traverse through every SPWeb of every SPSite of every SPWebApplication to see if the user has permissions to the site.

share|improve this answer
And if you do opt to iterate through all the SPWebs, SPSites, and SPWebApplications, BE SURE TO DISPOSE of the the SPWebs and SPSites so you don't end up with a StackOverflowException... – John Chapman May 10 '13 at 5:05
Thanks for your answer. Check my edit question please – Alvmad May 10 '13 at 8:40

John and Dave already told you it's not recommended to query SharePoint database however just to answer your question,

In SharePoint Config database there is a table named as dbo.SiteMap which has column ApplicationId which is actually id of all web application's in your farm including CA. You can get list of distinct application id's.

Since you got all applicationId's you can get web application object and then it's url using powershell,


SharePoint 2007: Getting object ID's using Sharepoint Central Administration


Approach is not recommened.

share|improve this answer
Thank you Muhammad – Alvmad May 10 '13 at 8:44

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.