0

I want to ask a Question, how to list by MS SQL, or to the Excel User Information List? With all fields of hiden List User Information List.

I menaged to list User Login, Name and Surname, and email, by this MS SQL Query:

    Declare @UserInfoID UniqueIdentifier
Declare @SiteID UniqueIdentifier
Declare @url nvarchar (100)

set @url = ''       
Select @SiteID=w.SiteId, @UserInfoID=s.UserInfoListId   
from WSS_Content.dbo.AllWebs W (nolock) join WSS_Content.dbo.AllSites S (nolock)  
on s.Id = w.siteid  
where w.fullurl = @url  
select ui.tp_id, ui.tp_login as UserLogin, ui.tp_title as UserName, ui.tp_email, UI.tp_Deleted, UI.tp_isactive, UI.tp_domaingroup, ui.tp_token, ui.tp_externaltoken, aud.*   
from WSS_Content.dbo.AllUserData AUD (nolock)  
join WSS_Content.dbo.userinfo (nolock) UI 
on AUD.tp_id = UI.tp_id and ui.tp_SiteID = aud.tp_SiteId  
where AUD.tp_ListId = @UserInfoID and tp_IsActive=1 -- UI.TP_Login Like '%ADAccount%'   
order by ui.tp_id

But How list much more fields ?

2
  • 1
    What kind of information do you want to retreive?
    – Benny Skogberg
    Aug 26, 2014 at 9:10
  • I want a everything Informations. I menaged this Query, but it only Gives me Login and Name, Surname, and Email Worker:
    – Grzegorz Z
    Aug 26, 2014 at 10:38

1 Answer 1

0

First, you should never query data from the SharePoint tables. There is only on database that is supported for this, reporting. For everything else you should use the API.

Have you tried the REST services? For example when I connect Excel 2013 (Data | Get External Data | Other | OData) to my site using https://tenantname.sharepoint.com/_vti_bin/listdata.svc and then grab the UserInformation list I see the following fields:

  • ContentTypeID
  • Name
  • Account
  • WorkEmail
  • MobilePhone
  • AboutMe
  • SIPAddress
  • IsSiteAdmin
  • Deleted
  • Hidden
  • Picture
  • Department
  • Title
  • FirstName
  • LastName
  • WorkPhone
  • UserName
  • WebSite
  • AskMeAbout
  • Office
  • PictureTimestamp
  • PicturePlaceholderState
  • PictureExchangeSyncState
  • Id
  • ContentType
  • Modified
  • Created
  • CreatedById
  • ModifiedById
  • Owshiddenversion
  • Version
  • Path

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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