Why is Sites.FullUrl null in SharePoint content database? Where to find the site collection url?

2
votes

I'm working on a sharepoint project.it needs use SQL query to get some information about site collection and web.I'm using sharepoint 2010:

SELECT
  Webs.Title AS [Site Title]
, Webs.FullUrl AS [Site Url]
, Lists.tp_Title AS [List/Library Title]
, COUNT(*) AS [Total GIF]
, CAST( CAST(SUM(Docs.Size) AS FLOAT) / 1024.0 AS DECIMAL(10,3) ) AS [Size (KB)]
FROM Docs
INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id 
INNER JOIN Lists ON Docs.ListId = Lists.tp_ID
WHERE Docs.Type <> 1 
AND Docs.LeafName LIKE '%.gif'
AND Docs.LeafName NOT LIKE 'template%'
GROUP BY
  Webs.FullUrl
, Webs.Title
, Lists.tp_Title
ORDER BY 
  [Site Url]
, [List/Library Title]
, [Total Gif]
, [Size (KB)]

when I check the content database directly, I found that Sites.FullUrl is always null, although I do have multiple site collections and webs (webs.FullUrl is not null).

I checked 2 Sharepoint 2010 I installed on VM, they are both null for Sites.FullUrl. Can someone confirm that Sites.FullUrl should not be blank? Is it because of sharepoint 2010?

Using powershell, I can get all info I want. So, I guess I should be able to get all the info from database directly too. Is this correct?

2
sql-serversharepointsharepoint-2010
added a query in the question. thanks – urlreader

2 Answers

1
votes

It's not recommended that you query the content database. You're kinda supposed to treat it as a black box, as explained in this other question.

If you need some way of dynamically retrieving a SPSite's url from an external application, you could store the url in a list and then retrieve it using list web service calls.

1
votes

If you're looking for the URL of the site-collection, you can use Sites.RootWebId to join with Webs, and use Webs.FullUrl.

For example:

select SiteID=Sites.ID
    , RootWebId=Sites.RootWebId
    , RootWebURL = Webs.FullUrl
from Sites 
    inner join Webs on Sites.RootWebId = Webs.Id