2

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?

Marek Grzenkowicz
  • 17,024
  • 9
  • 81
  • 111
urlreader
  • 6,319
  • 7
  • 57
  • 91

2 Answers2

1

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.

Community
  • 1
  • 1
Kache
  • 15,647
  • 12
  • 51
  • 79
1

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
jeffderrick
  • 31
  • 3