Logo 
Search:

Sharepoint Forum

 Ask Question   UnAnswered
Home » Forum » Sharepoint       RSS Feeds

xxx_Site data dictionary

  Asked By: Claude    Date: Jan 05    Category: Sharepoint    Views: 1410

does anyone have the link that shows a data dictionary of the site database?

what i want to do is perform a sql query to list ALL sites (not just top level) and the members along with thier roles..

anyone by chance gotten that?

ive kind of gotten a query that will return all the sites - this is where im at now (dba's please dont laugh):

SELECT
d.DirName AS Site,
d.LeafName AS DocName,
d.Size AS Size,
d.TimeCreated AS Created,
d.timelastmodified AS modified

FROM Docs d

WHERE d.leafname = 'default.aspx'
and d.dirname like 'sites/%'
ORDER BY Site

-----

so my rudimentary caveman query will return all sites and subsites, basically anything with a default.aspx and a dirname that starts with /sites.

anyone have any ideas? i have been asked to produce a report with a summary of each site and thier users along with roles, considering my query returns 201 sites - thats going to take a while going through the user interface and copy/pasting..

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Horace Coffey     Answered On: Jan 05

And you don’t want to write to the API because?

 
Answer #2    Answered By: Rigoberto Beard     Answered On: Jan 05

to be honest, i know more about sql  than i do about all this asp stuff

 
Answer #3    Answered By: Alphonso Mckay     Answered On: Jan 05

Here is what you need. I happened to have this in my "library" of
useful SQL queries. Also, the data  dictionary is located at
msdn.microsoft.com/.../en-
us/spptsdk/html/tsdbTables_SV01051532.asp

SELECT DISTINCT dbo.Webs.FullUrl, dbo.WebGroups.Title AS [Group],
dbo.WebGroups.Type, dbo.UserInfo.tp_Title
FROM dbo.Webs RIGHT OUTER JOIN
dbo.WebGroups INNER JOIN
dbo.WebGroupMembership ON dbo.WebGroups.WebID
= dbo.WebGroupMembership.WebID AND
dbo.WebGroups.ID =
dbo.WebGroupMembership.GroupID INNER JOIN
dbo.UserInfo ON
dbo.WebGroupMembership.MemberID = dbo.UserInfo.tp_ID ON
dbo.Webs.SiteId = dbo.UserInfo.tp_SiteID AND
dbo.Webs.Id = dbo.WebGroups.WebID
WHERE (dbo.Webs.FullUrl LIKE N'%sites%')
ORDER BY dbo.WebGroups.Title

 
Didn't find what you were looking for? Find more on xxx_Site data dictionary Or get search suggestion and latest updates.




Tagged:      

 
 
 
 

Related Post