Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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

EDIT

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, 
        Webs.FullURL
FROM AllUserData AS UserData 
    LEFT OUTER LOOP JOIN AllUserData AS t2
            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] = 'usernam2@mail.com' 
        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
2  
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 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 at 9:11
add comment

3 Answers

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.

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server.UserProfiles")


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

    $obj 
}
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
add comment

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 http://msdn.microsoft.com/en-us/library/jj163818.aspx

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
add comment

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,

$a=Get-SPWebApplication(“http://xxxxxxx&#8221;)
$a.ID

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

Note:

Approach is not recommened.

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

Your Answer

 
discard

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.