none
sharepoint [user information list] column names

    Question

  • I am able to query the [User Information List] via code in Visual Studio but I cannot find the actual column names for the table. I can also work with this table via Access 2007 Linked to SharePoint. It uses friendly names as opposed to the actual names like "ows_name" etc. Is there a list somewhere that has the actual default column names? I can access this list via code or Access 2007 linked tables but for some reason CAML query builder and Stramit.CamlViewer2007 give me access denied. I also get the same message if I go to http://thewebsite/_catalogs/users/simple.aspx .

    So I am unable to see the column names. In code I am not having luck guessing the names. Of course it woudl have been great for Microsoft to just have a field that is the name called name but that would be to easy :)
    Wednesday, July 7, 2010 9:15 PM

Answers

  • Hi belcherman,

     

    Thanks for you post.

     

    I think you can get the column name of the “User Information List” by SharePoint OM.  Here is some sample code, hope it’s helpful.

    public void GetUserInfoColumns(string WebUrl)

            {

                SPSecurity.RunWithElevatedPrivileges(delegate()

                {

                    using (SPSite site = new SPSite(WebUrl))

                    {

                        using (SPWeb web = site.OpenWeb())

                        {

                            SPList userList = web.Lists["User Information List"];

                            SPFieldCollection fieldColl = userList.Fields;

                            foreach (SPField field in fieldColl)

                            {

                                Console.WriteLine("Name:{0};Type:{1}", field.Title, field.TypeAsString);

                            }

                        }

                    }               

                });

            }

    And this is the result:

    Name:Content Type ID;Type:ContentTypeId

    Name:Name;Type:Text

    Name:Approver Comments;Type:Note

    Name:File Type;Type:Text

    Name:Account;Type:Text

    Name:Work e-mail;Type:Text

    Name:About me;Type:Note

    Name:SIP Address;Type:Text

    Name:Locale;Type:Integer

    Name:Calendar Type;Type:Integer

    Name:AdjustHijriDays;Type:Integer

    Name:TimeZone;Type:Integer

    Name:Time Format;Type:Boolean

    Name:Alternate Calendar Type;Type:Integer

    Name:CalendarView Options;Type:Integer

    Name:WorkDays;Type:Integer

    Name:WorkDay StartHour;Type:Integer

    Name:WorkDay EndHour;Type:Integer

    Name:Is Site Admin;Type:Boolean

    Name:Deleted;Type:Boolean

    Name:Picture;Type:URL

    Name:Department;Type:Text

    Name:Title;Type:Text

    Name:Is Active;Type:Boolean

    Name:First name;Type:Text

    Name:Last name;Type:Text

    Name:Name;Type:Computed

    Name:Work phone;Type:Text

    Name:Office;Type:Text

    Name:User name;Type:Text

    Name:Web site;Type:URL

    Name:Responsibilities;Type:Note

    Name:MyWork Email;Type:Note

    Name:ID;Type:Counter

    Name:Content Type;Type:Text

    Name:Modified;Type:DateTime

    Name:Created;Type:DateTime

    Name:Created By;Type:User

    Name:Modified By;Type:User

    Name:Has Copy Destinations;Type:Boolean

    Name:Copy Source;Type:Text

    Name:owshiddenversion;Type:Integer

    Name:Workflow Version;Type:Integer

    Name:UI Version;Type:Integer

    Name:Version;Type:Text

    Name:Attachments;Type:Attachments

    Name:Approval Status;Type:ModStat

    Name:Edit;Type:Computed

    Name:Name;Type:Computed

    Name:Select;Type:Computed

    Name:Instance ID;Type:Integer

    Name:Order;Type:Number

    Name:GUID;Type:Guid

    Name:Workflow Instance ID;Type:Guid

    Name:URL Path;Type:Lookup

    Name:Path;Type:Lookup

    Name:Modified;Type:Lookup

    Name:Created;Type:Lookup

    Name:Item Type;Type:Lookup

    Name:Effective Permissions Mask;Type:Computed

    Name:Name;Type:File

    Name:Unique Id;Type:Lookup

    Name:ProgId;Type:Lookup

    Name:ScopeId;Type:Lookup

    Name:HTML File Type;Type:Computed

    Name:Edit Menu Table Start;Type:Computed

    Name:Edit Menu Table End;Type:Computed

    Name:Name;Type:Computed

    Name:Name;Type:Computed

    Name:Type;Type:Computed

    Name:Server Relative URL;Type:Computed

    Name:Encoded Absolute URL;Type:Computed

    Name:File Name;Type:Computed

    Name:Property Bag;Type:Lookup

    Name:Level;Type:Integer

    Name:Is Current Version;Type:Boolean

    Name:Group;Type:Computed

    Name:Edit;Type:Computed

    Name:Name;Type:Computed

    Name:Picture;Type:Computed

    Name:Name;Type:Computed

    Name:Name;Type:Computed

    Name:Edit;Type:Computed

    Name:Selection Checkbox;Type:Computed

    Name:Content Type;Type:Computed

    • Marked as answer by Wayne Fan Monday, July 12, 2010 1:59 AM
    Friday, July 9, 2010 1:32 AM
  • Hey THanks, I also figured out by going to MSDN that when using CAML and XML that you need to prefix the internal field name with "ows_". Once I had the internal names and then did my checks with the prefix it all worked.
    • Marked as answer by Wayne Fan Monday, July 12, 2010 1:59 AM
    Friday, July 9, 2010 1:27 PM

All replies

  • You will have to be a site collection administrator to view any of those fields with any of the tools.
    certdev.com
    Thursday, July 8, 2010 12:29 AM
  • Is the [User Information List] setup with a standard set of columns on all installs? I just need the field names.

    In Access 2007 I can query for the fields I need with the following:

    strSQLstring =

    "SELECT ID, [Content Type], Name, Account, [E-Mail], Picture, Department, [Job Title] FROM [User Information List] WHERE ([Content Type] = 'Person') ORDER BY Name";

    This is what I need to pull out of the list.


    I am trying to do this via SharePoint Web Services now but it is getting strange results. The field names are different in the Database so things are showing up or not at all.



    ows_ID comes out correct. ows_Title shows the persons name instead of ows_Name. ows_Name is showing the username/domain accoutn instead. I just don't know the field names.


    I am testing with:

     

    string str_ows_ID = "";
    string str_ows_Name = "";

    ResumeDatabaseLists.

    Lists myservice = new ResumeDatabaseLists.Lists();

     

    // default credentials - this is the person logged in.

     

    //myservice.Credentials = System.Net.CredentialCache.DefaultCredentials;

     

    // Account with full control

    myservice.Credentials =

    new NetworkCredential("userr", "pass", "domain");

    myservice.Url =

    "http://website/HR/_vti_bin/lists.asmx";

     

    string listName = "{0000000-EF88-4B0D-932B-92A0BC64E79F}";

     

    string viewName = string.Empty;

     

    string rowLimit = "100";

     

     

    // Instantiate an XmlDocument object

    System.Xml.

    XmlDocument xmlDoc = new System.Xml.XmlDocument();

    System.Xml.

    XmlElement query = xmlDoc.CreateElement("Query");

    System.Xml.

    XmlElement viewFields = xmlDoc.CreateElement("ViewFields");

    System.Xml.

    XmlElement queryOptions = xmlDoc.CreateElement("QueryOptions");


    strCAMLquery = strCAMLquery +

    "<Query>";

    strCAMLquery +=

    "<Where>";

    strCAMLquery +=

    "<IsNotNull>";

    strCAMLquery +=

    "<FieldRef Name='ID' />";

    strCAMLquery +=

    "</IsNotNull>";

    strCAMLquery +=

    "</Where>";

    strCAMLquery +=

    "</Query>";



     

    /*Use CAML query*/

    query.InnerXml = strCAMLquery;

     

    // FIELDS

    viewFields.InnerXml =

    "<FieldRef Name='ID' /><FieldRef Name='Name' />";

    queryOptions.InnerXml =

    "";

     

     

    System.Xml.XmlNode nodes = myservice.GetListItems(listName, viewName, query, viewFields, rowLimit, null, null);




     

    foreach (System.Xml.XmlNode node in nodes)

    {

     

    if (node.Name == "rs:data")

    {

     

    for (int i = 0; i < node.ChildNodes.Count; i++)

    {

     

     

     

     

    // DISPLAY THE RESULTS

     

    if (node.ChildNodes[i].Name == "z:row")

    {

     

     

     

     

    if (node.ChildNodes[i].Attributes["ows_ID"] == null)

    {

    str_ows_ID =

    "ID Missing";

    }

     

    else

    {

    str_ows_ID = node.ChildNodes[i].Attributes[

    "ows_ID"].Value;

    }

     

     

     

     

    if (node.ChildNodes[i].Attributes["ows_Name"] == null)

    {

    str_ows_Name =

    "Name Missing";

    }

     

    else

    {

    str_ows_Name = node.ChildNodes[i].Attributes[

    "ows_Name"].Value;

    strClean = str_ows_Name.Substring(str_ows_Name.IndexOf(

    "#") + 1);

    str_ows_Name = strClean;

    }

     

     

     

    // need to get this from USER INFORMATION LIST JOB_TITLE

     

    if (node.ChildNodes[i].Attributes["ows_Title"] == null)

    {

    str_ows_Position_x0020_Category =

    "Title Missing";

    }

     

    else

    {

    str_ows_Position_x0020_Category = node.ChildNodes[i].Attributes[

    "ows_Title"].Value;

    strClean = str_ows_Position_x0020_Category.Substring(str_ows_Position_x0020_Category.IndexOf(

    "#") + 1);

    str_ows_Position_x0020_Category = strClean;

    }

     

     

     

     

    if (node.ChildNodes[i].Attributes["ows_Modified"] == null)

    {

    str_ows_Modified =

    "Modified Missing";

    }

     

    else

    {

    str_ows_Modified = node.ChildNodes[i].Attributes[

    "ows_Modified"].Value;

    strClean = str_ows_Modified;

    str_ows_Modified = strClean;

    }

     

     

    if (node.ChildNodes[i].Attributes["ows_Biographical_x0020_Sketch"] == null)

    {

    str_ows_Biographical_x0020_Sketch =

    "Bio Missing";

    }

     

    else

    {

    str_ows_Biographical_x0020_Sketch = node.ChildNodes[i].Attributes[

    "ows_Biographical_x0020_Sketch"].Value;

     

    //strClean = str_ows_Modified;

     

    //str_ows_Modified = strClean;

    }

     

     

     

     

     

    strSharepointListsXML +=

    "<div align='center'>" + strNL;

    strSharepointListsXML +=

    "<table border='1' width='770' cellspacing='5' cellpadding='5'>" + strNL;

    strSharepointListsXML +=

    "<tr>" + strNL;

    strSharepointListsXML +=

    "<td align='center' width='20'><b><font color='#000080'>" + str_ows_ID + "</font></b></td>" + strNL;

    strSharepointListsXML +=

    "<td align='center' width='225'><b><font color='#000080'>" + str_ows_Name + "</font></b></td>" + strNL;

    strSharepointListsXML +=

    "<td align='center' width='269'><b><font color='#000080'>" + str_ows_Position_x0020_Category + "</font></b></td>" + strNL;

    strSharepointListsXML +=

    "<td align='center' width='181'><font color='#000080'>" + str_ows_Modified + "</b></font></td>" + strNL;

    strSharepointListsXML +=

    "</tr>" + strNL;

    strSharepointListsXML +=

    "<tr>" + strNL;

    strSharepointListsXML +=

    "<td align='left' colspan='4'>" + strNL;

    strSharepointListsXML +=

    "<b><font color='#000080'>BIOLOGICAL SKETCH:</font></b><br>" + strNL;

    strSharepointListsXML += str_ows_Biographical_x0020_Sketch + strNL; ;

    strSharepointListsXML +=

    "</td>" + strNL;

    strSharepointListsXML +=

    "</tr>" + strNL;

    strSharepointListsXML +=

    "</table>" + strNL;

    strSharepointListsXML +=

    "<br>" + strNL;

    strSharepointListsXML +=

    "<br>" + strNL;

    strSharepointListsXML +=

    "</div>" + strNL;

     

     

    }

     

     

    }

    }

    }

    // END foreach (System.Xml.XmlNode node in nodes)













    Thursday, July 8, 2010 1:39 PM
  • Hi belcherman,

     

    Thanks for you post.

     

    I think you can get the column name of the “User Information List” by SharePoint OM.  Here is some sample code, hope it’s helpful.

    public void GetUserInfoColumns(string WebUrl)

            {

                SPSecurity.RunWithElevatedPrivileges(delegate()

                {

                    using (SPSite site = new SPSite(WebUrl))

                    {

                        using (SPWeb web = site.OpenWeb())

                        {

                            SPList userList = web.Lists["User Information List"];

                            SPFieldCollection fieldColl = userList.Fields;

                            foreach (SPField field in fieldColl)

                            {

                                Console.WriteLine("Name:{0};Type:{1}", field.Title, field.TypeAsString);

                            }

                        }

                    }               

                });

            }

    And this is the result:

    Name:Content Type ID;Type:ContentTypeId

    Name:Name;Type:Text

    Name:Approver Comments;Type:Note

    Name:File Type;Type:Text

    Name:Account;Type:Text

    Name:Work e-mail;Type:Text

    Name:About me;Type:Note

    Name:SIP Address;Type:Text

    Name:Locale;Type:Integer

    Name:Calendar Type;Type:Integer

    Name:AdjustHijriDays;Type:Integer

    Name:TimeZone;Type:Integer

    Name:Time Format;Type:Boolean

    Name:Alternate Calendar Type;Type:Integer

    Name:CalendarView Options;Type:Integer

    Name:WorkDays;Type:Integer

    Name:WorkDay StartHour;Type:Integer

    Name:WorkDay EndHour;Type:Integer

    Name:Is Site Admin;Type:Boolean

    Name:Deleted;Type:Boolean

    Name:Picture;Type:URL

    Name:Department;Type:Text

    Name:Title;Type:Text

    Name:Is Active;Type:Boolean

    Name:First name;Type:Text

    Name:Last name;Type:Text

    Name:Name;Type:Computed

    Name:Work phone;Type:Text

    Name:Office;Type:Text

    Name:User name;Type:Text

    Name:Web site;Type:URL

    Name:Responsibilities;Type:Note

    Name:MyWork Email;Type:Note

    Name:ID;Type:Counter

    Name:Content Type;Type:Text

    Name:Modified;Type:DateTime

    Name:Created;Type:DateTime

    Name:Created By;Type:User

    Name:Modified By;Type:User

    Name:Has Copy Destinations;Type:Boolean

    Name:Copy Source;Type:Text

    Name:owshiddenversion;Type:Integer

    Name:Workflow Version;Type:Integer

    Name:UI Version;Type:Integer

    Name:Version;Type:Text

    Name:Attachments;Type:Attachments

    Name:Approval Status;Type:ModStat

    Name:Edit;Type:Computed

    Name:Name;Type:Computed

    Name:Select;Type:Computed

    Name:Instance ID;Type:Integer

    Name:Order;Type:Number

    Name:GUID;Type:Guid

    Name:Workflow Instance ID;Type:Guid

    Name:URL Path;Type:Lookup

    Name:Path;Type:Lookup

    Name:Modified;Type:Lookup

    Name:Created;Type:Lookup

    Name:Item Type;Type:Lookup

    Name:Effective Permissions Mask;Type:Computed

    Name:Name;Type:File

    Name:Unique Id;Type:Lookup

    Name:ProgId;Type:Lookup

    Name:ScopeId;Type:Lookup

    Name:HTML File Type;Type:Computed

    Name:Edit Menu Table Start;Type:Computed

    Name:Edit Menu Table End;Type:Computed

    Name:Name;Type:Computed

    Name:Name;Type:Computed

    Name:Type;Type:Computed

    Name:Server Relative URL;Type:Computed

    Name:Encoded Absolute URL;Type:Computed

    Name:File Name;Type:Computed

    Name:Property Bag;Type:Lookup

    Name:Level;Type:Integer

    Name:Is Current Version;Type:Boolean

    Name:Group;Type:Computed

    Name:Edit;Type:Computed

    Name:Name;Type:Computed

    Name:Picture;Type:Computed

    Name:Name;Type:Computed

    Name:Name;Type:Computed

    Name:Edit;Type:Computed

    Name:Selection Checkbox;Type:Computed

    Name:Content Type;Type:Computed

    • Marked as answer by Wayne Fan Monday, July 12, 2010 1:59 AM
    Friday, July 9, 2010 1:32 AM
  • Hey THanks, I also figured out by going to MSDN that when using CAML and XML that you need to prefix the internal field name with "ows_". Once I had the internal names and then did my checks with the prefix it all worked.
    • Marked as answer by Wayne Fan Monday, July 12, 2010 1:59 AM
    Friday, July 9, 2010 1:27 PM
  • Has anybody tried using SSIS and the SharepointList Source dataflow task for this? I think it should work more straightforward..

    Just a thought..

    Cheers'

    Monday, March 12, 2012 3:49 PM
  • StaticName = ContentTypeId , Title = ID do Tipo de Conteúdo, Type = ContentTypeId
    StaticName = Title , Title = Nome, Type = Text
    StaticName = _ModerationComments , Title = Comentários do Aprovador, Type = Note
    StaticName = File_x0020_Type , Title = Tipo de Arquivo, Type = Text
    StaticName = Name , Title = Conta, Type = Text
    StaticName = EMail , Title = Email, Type = Text
    StaticName = MobilePhone , Title = Telefone Celular, Type = Text
    StaticName = Notes , Title = Sobre Mim, Type = Note
    StaticName = SipAddress , Title = Endereço SIP, Type = Text
    StaticName = Locale , Title = Localidade, Type = Integer
    StaticName = CalendarType , Title = Tipo de Calendário, Type = Integer
    StaticName = AdjustHijriDays , Title = AjustarDiasIslâmicos, Type = Integer
    StaticName = TimeZone , Title = FusoHorário, Type = Integer
    StaticName = Time24 , Title = Formato de Hora, Type = Boolean
    StaticName = AltCalendarType , Title = Tipo de Calendário Alternativo, Type = Integer
    StaticName = CalendarViewOptions , Title = Opções de Exibição de Calendário, Type = Integer
    StaticName = WorkDays , Title = DiasDeTrabalho, Type = Integer
    StaticName = WorkDayStartHour , Title = HoraDeInício do DiaDeTrabalho, Type = Integer
    StaticName = WorkDayEndHour , Title = HoraDeTérmino do DiaDeTrabalho, Type = Integer
    StaticName = IsSiteAdmin , Title = É Administrador do Site, Type = Boolean
    StaticName = Deleted , Title = Excluído, Type = Boolean
    StaticName = Picture , Title = Imagem, Type = URL
    StaticName = Department , Title = Departamento, Type = Text
    StaticName = JobTitle , Title = Cargo, Type = Text
    StaticName = IsActive , Title = Está Ativo, Type = Boolean
    StaticName = WorkPhone , Title = Telefone Comercial, Type = Text
    StaticName = ID , Title = ID, Type = Counter
    StaticName = ContentType , Title = Tipo de Conteúdo, Type = Computed
    StaticName = Modified , Title = Modificado, Type = DateTime
    StaticName = Created , Title = Criado, Type = DateTime
    StaticName = Author , Title = Criado por, Type = User
    StaticName = Editor , Title = Modificado por, Type = User
    StaticName = _HasCopyDestinations , Title = Tem Destinos de Cópia, Type = Boolean
    StaticName = _CopySource , Title = Origem da Cópia, Type = Text
    StaticName = owshiddenversion , Title = owshiddenversion, Type = Integer
    StaticName = WorkflowVersion , Title = Versão do Fluxo de Trabalho, Type = Integer
    StaticName = _UIVersion , Title = Versão da UI, Type = Integer
    StaticName = _UIVersionString , Title = Versão, Type = Text
    StaticName = Attachments , Title = Anexos, Type = Attachments
    StaticName = _ModerationStatus , Title = Status de Aprovação, Type = ModStat
    StaticName = Edit , Title = Editar, Type = Computed
    StaticName = LinkTitleNoMenu , Title = Nome, Type = Computed
    StaticName = LinkTitle , Title = Título, Type = Computed
    StaticName = LinkTitle2 , Title = Título, Type = Computed
    StaticName = SelectTitle , Title = Selecionar, Type = Computed
    StaticName = InstanceID , Title = ID da Instância, Type = Integer
    StaticName = Order , Title = Ordem, Type = Number
    StaticName = GUID , Title = GUID, Type = Guid
    StaticName = WorkflowInstanceID , Title = ID da Instância do Fluxo de Trabalho, Type = Guid
    StaticName = FileRef , Title = Caminho da URL, Type = Lookup
    StaticName = FileDirRef , Title = Caminho, Type = Lookup
    StaticName = Last_x0020_Modified , Title = Modificado, Type = Lookup
    StaticName = Created_x0020_Date , Title = Criado, Type = Lookup
    StaticName = FSObjType , Title = Tipo de Item, Type = Lookup
    StaticName = SortBehavior , Title = Tipo de Classificação, Type = Lookup
    StaticName = PermMask , Title = Máscara de Permissões Efetivas, Type = Computed
    StaticName = FileLeafRef , Title = Nome, Type = File
    StaticName = UniqueId , Title = Id Exclusiva, Type = Lookup
    StaticName = SyncClientId , Title = ID de Cliente, Type = Lookup
    StaticName = ProgId , Title = ProgId, Type = Lookup
    StaticName = ScopeId , Title = ScopeId, Type = Lookup
    StaticName = HTML_x0020_File_x0020_Type , Title = Tipo de Arquivo HTML, Type = Computed
    StaticName = _EditMenuTableStart , Title = Início da Tabela do Menu de Edição, Type = Computed
    StaticName = _EditMenuTableStart2 , Title = Início da Tabela do Menu de Edição, Type = Computed
    StaticName = _EditMenuTableEnd , Title = Fim da Tabela do Menu de Edição, Type = Computed
    StaticName = LinkFilenameNoMenu , Title = Nome, Type = Computed
    StaticName = LinkFilename , Title = Nome, Type = Computed
    StaticName = LinkFilename2 , Title = Nome, Type = Computed
    StaticName = DocIcon , Title = Tipo, Type = Computed
    StaticName = ServerUrl , Title = URL Relativa do Servidor, Type = Computed
    StaticName = EncodedAbsUrl , Title = URL Absoluta Codificada, Type = Computed
    StaticName = BaseName , Title = Nome do Arquivo, Type = Computed
    StaticName = MetaInfo , Title = Conjunto de Propriedades, Type = Lookup
    StaticName = _Level , Title = Nível, Type = Integer
    StaticName = _IsCurrentVersion , Title = É a Versão Atual, Type = Boolean
    StaticName = ItemChildCount , Title = Contagem de Itens Filhos, Type = Lookup
    StaticName = FolderChildCount , Title = Contagem de Elementos Filho da Pasta, Type = Lookup
    StaticName = GroupLink , Title = Grupo, Type = Computed
    StaticName = GroupEdit , Title = Editar, Type = Computed
    StaticName = ImnName , Title = Nome, Type = Computed
    StaticName = PictureDisp , Title = Imagem, Type = Computed
    StaticName = NameWithPicture , Title = Nome, Type = Computed
    StaticName = NameWithPictureAndDetails , Title = Nome, Type = Computed
    StaticName = EditUser , Title = Editar, Type = Computed
    StaticName = UserSelection , Title = Caixa de Seleção, Type = Computed
    StaticName = ContentTypeDisp , Title = Tipo de Conteúdo, Type = Computed
    Friday, August 3, 2012 8:50 PM
  • Hi Wayne,

    Good answer but with your permission allow me to submit my own, a bit more simplified.

    I've found that I didn't need to elevate permissions for this.

    Also, you should probably ask to display intermal names while you're at it.

    Another point is that "web.Lists["User Information List"]" Also will not work in all cases (depends on the version?).

    If it doesn't, you can use this: web.SiteUserInfoList

    Here's my modified version (tested working on premises SP13).

    It's VB.net but you get the idea...

    Public Sub GetUserInfoColumns()
            Using site As New SPSite("http://SiteName")
                Using web As SPWeb = site.OpenWeb()
                    Dim userList As SPList = web.SiteUserInfoList
                    Dim fieldColl As SPFieldCollection = userList.Fields
                    For Each field As SPField In fieldColl
                        Console.WriteLine("Name:{0};Type:{1}", field.InternalName, field.TypeAsString)
                    Next
                End Using
            End Using
        End Sub


    • Proposed as answer by AdiSolar Tuesday, September 27, 2016 7:08 AM
    Tuesday, September 27, 2016 7:07 AM