I too am looking for something like this, can anyone point me in the right direction?

Hi,

 I hope you can get it by using ( just this an rough idea  )

                                                        SELECT  < all The properties what you want>

                                                       FROM   < The Members table> // where the list of users existing// ;

 Here you need not to use WHERE condition as you want to select all members.

For further more SQL questions  <l> http://www.wiziq.com/online-tests/11428-sql-quiz-1 ; </l>

aah, that only returns the basic member properties, sorry, I should have said, I'm actually after custom member properties.

Hey Shaun (and skiltz), are you sure you want it a SQL script for this? Seriously, get ready to open a can-of-worms!!!

Here's a little something I wrote for a client a year ago...

SELECT
    mt.pk,
    d.contentNodeId,
    u.text,
    m.Email,
    m.LoginName,
    mt.propertytypeId,
    p.tabId,
    t.text AS tabName,
    p.Alias,
    p.Name,
    p.dataTypeId,
    dt.dbType,
    dt.controlId,
    d.id AS propertyId,
    d.dataInt,
    d.dataDate,
    d.dataNvarchar,
    d.dataNtext,
    mt.viewOnProfile,
    p.sortOrder
FROM
    dbo.cmsMemberType AS mt INNER JOIN
    dbo.cmsPropertyType AS p ON mt.propertytypeId = p.id INNER JOIN
    dbo.cmsDataType AS dt ON p.dataTypeId = dt.nodeId INNER JOIN
    dbo.cmsTab AS t ON p.tabId = t.id AND mt.NodeId = t.contenttypeNodeId INNER JOIN
    dbo.cmsPropertyData AS d ON p.id = d.propertytypeid INNER JOIN
    dbo.cmsMember AS m ON m.nodeId = d.contentNodeId INNER JOIN
    dbo.umbracoNode AS u ON u.id = m.nodeId

It's a beast of INNER JOINs, not a big fan of using it... but was forced into it by the client and other technical restrictions at the time.

Ideally, I'd recommend that you use the ASP.NET Membership Provider API to retrieve member data ... but I'm also realistic, sometimes you need to go for the raw data - just to get the job done.

Good luck with the SQL script, I can't promise that its going to do what you need it for ... but it's a start.

Cheers, Lee.

hehe, cheers Lee. I used something similar in the end.

It was only for a one-off data grab, so the beastliness of it wasn't really an issue for me.

This is what I use.

create table myXML (xm_data xml)
insert into MyXML (xm_data) 
select 
  cmsContentXml.xml from cmsContentXML inner join cmsContent c on c.nodeid = cmsContentXml.nodeid
  where c.contentType =  1071
  
select 
tab.col.value('@nodeName','varchar(50)') as 'ID',
tab.col.value('@email','varchar(50)') as 'Email',
tab.col.value('data[@alias][1]','varchar(50)') + ' ' + tab.col.value('data[@alias][2]','varchar(50)') as "Name",
tab.col.value('data[@alias][1]','varchar(50)') as 'First Name',
tab.col.value('data[@alias][2]','varchar(50)') as 'Last Name',
tab.col.value('data[@alias][3]','varchar(50)') as 'Home Phone',
tab.col.value('data[@alias][4]','varchar(50)') as 'Mobile Phone',
tab.col.value('data[@alias][5]','varchar(50)') as 'Address',
tab.col.value('data[@alias][6]','varchar(50)') as 'AvartarMediaID',
tab.col.value('data[@alias][7]','varchar(50)') as 'ShowOnProfile'
from myXML cross apply xm_data.nodes('node') tab (col)




drop table MyXml

Thanks Lee - you saved me a lot of tedious work

Can of worms as may be, but it's a very good technique when the client has asked for a list of everyone missing a particular property :o)

Thanks Lee, worked perfectly.

For anyone else using MySQL, just lose the dbo.

following on from lee's snippet, slight tweak for later versions (as tab table has gone) and filtering on username

SELECT        mt.pk, d.contentNodeId, u.text, m.Email, m.LoginName, mt.propertytypeId, p.Alias, p.Name, p.dataTypeId, dt.dbType, d.id AS propertyId, d.dataInt, d.dataDate, d.dataNvarchar, d.dataNtext, mt.viewOnProfile, 
                         p.sortOrder
FROM            dbo.cmsMemberType AS mt INNER JOIN
                         dbo.cmsPropertyType AS p ON mt.propertytypeId = p.id INNER JOIN
                         dbo.cmsDataType AS dt ON p.dataTypeId = dt.nodeId INNER JOIN
                         dbo.cmsPropertyData AS d ON p.id = d.propertytypeid INNER JOIN
                         dbo.cmsMember AS m ON m.nodeId = d.contentNodeId INNER JOIN
                         dbo.umbracoNode AS u ON u.id = m.nodeId
WHERE        (m.LoginName = N'[email protected]')

If you have a LOT of fields in the member record, it doesn't work because they query is longer than what fits in the NVARCHAR(max)

I've updated Tim's script above to work with Umbraco 8 - theres a minor change that lets you target a specific member type as opposed to all member types (which creates duplicates columns) so first you need to run this little SQL to get the unique ID eg

select * from dbo.umbracoNode
where nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43' 

Then grab the uniqueID for the Member Type you are interested in. Simply modify the script below with your own unique id. Note that I commented out the member groups because I just wanted one row per member and some of my members are in multiple groups

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);

SET @cols = STUFF((
            SELECT ',' + QUOTENAME(pt.Name)
            FROM
                (SELECT [Id] FROM dbo.umbracoNode WHERE uniqueId = 'A062878F-B37B-4B14-A818-E9E994262F18') AS mt
                LEFT OUTER JOIN (SELECT NodeID, contentTypeId FROM dbo.umbracoContent) AS ml ON ml.contentTypeId = mt.id
                LEFT JOIN dbo.cmsPropertyType AS pt ON pt.contentTypeId = ml.contentTypeId

            GROUP BY pt.Name, pt.sortOrder, ',' + QUOTENAME(pt.Name)
            ORDER BY pt.Name, pt.sortOrder ASC
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')


set @query = '
SELECT
    NodeId,
    LoginName,
    Email,
    createDate,
    /*MemberGroup,*/
    ' + @cols + '
FROM
(
    SELECT 
    pt.Name AS MemberFieldName
    ,    ISNULL(CASE
            WHEN dt.DBTYPE = ''Ntext'' THEN CAST(d.[textValue] AS NVARCHAR(MAX))
        WHEN dt.DBTYPE = ''Nvarchar'' THEN d.varcharValue
        WHEN dt.DBTYPE = ''Date'' THEN CONVERT(nvarchar, d.[dateValue])
        WHEN dt.DBTYPE = ''Integer'' THEN CONVERT(nvarchar, d.[intValue])
        ELSE NULL
    END, NULL)
    AS MemberData
,    m.nodeId
,    m.LoginName
,    m.Email
,    n.createDate
/*,   g.[Text] AS MemberGroup*/
FROM
    (SELECT [Id] FROM dbo.umbracoNode WHERE uniqueid = ''A062878F-B37B-4B14-A818-E9E994262F18'') AS mt
        LEFT OUTER JOIN (SELECT NodeID, contentTypeId FROM dbo.umbracoContent) AS ml ON ml.contentTypeId = mt.id
        LEFT JOIN dbo.cmsPropertyType AS pt ON pt.contentTypeId = ml.contentTypeId
        LEFT JOIN [dbo].[umbracoDataType] AS dt ON pt.datatypeID = dt.NodeId
        LEFT JOIN dbo.cmsMember AS m ON m.NodeID = ml.NodeID
        LEFT JOIN dbo.umbracoContentVersion AS cv ON cv.nodeId = m.nodeId
        LEFT JOIN dbo.umbracoPropertyData AS d ON d.versionId = cv.id AND d.propertytypeid = pt.id
        LEFT JOIN dbo.cmsMember2MemberGroup AS xmg ON xmg.Member = m.NodeID
        LEFT JOIN dbo.umbracoNode AS g ON g.id = xmg.MemberGroup
        LEFT JOIN dbo.umbracoNode AS n ON n.id = m.nodeId

) As src
PIVOT (
    MAX(MemberData)
    FOR MemberFieldName in (' + @cols + ')
) aS pvt
ORDER BY LoginName'

print(@query)
execute(@query);

Hope this helps somebody :)

@mouseball

Big thanks! great share! I wonder how I could filter out only the approved members (umbracoMemberApproved = 1)

Don't know If any can use this. :)

I made it for Umbraco 8 and can't fetch custom data, you just need to know the type of field. [varcharValue], [intValue] etc and the alias field

/****** Get All members  ******/
SELECT TOP (300000) 
[dbo].[cmsMember].nodeId
,[Node].createDate as createDate
,[version].versionDate as UpdateDate
,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
    pt.id as propertyId
        FROM [dbo].[umbracoContent] 
        Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
        Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
        where pt.Alias = 'firstname') And versionId = [version].id) as FirstName
,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
    pt.id as propertyId
        FROM [dbo].[umbracoContent] 
        Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
        Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
        where pt.Alias = 'lastname') And versionId = [version].id) as LastName
,m.Email as Email
,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
    pt.id as propertyId
        FROM [dbo].[umbracoContent] 
        Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
        Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
        where pt.Alias = 'landingCodePhoneNumber') And versionId = [version].id) as LandCode
,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
    pt.id as propertyId
        FROM [dbo].[umbracoContent] 
        Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
        Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
        where pt.Alias = 'mobileNumber') And versionId = [version].id) as Mobile
,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
    pt.id as propertyId
        FROM [dbo].[umbracoContent] 
        Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
        Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
        where pt.Alias = 'streetname') And versionId = [version].id) as StreetName
,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
    pt.id as propertyId
        FROM [dbo].[umbracoContent] 
        Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
        Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
        where pt.Alias = 'streetNumber') And versionId = [version].id) as StreetNumber
,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
    pt.id as propertyId
        FROM [dbo].[umbracoContent] 
        Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
        Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
        where pt.Alias = 'streetLetter') And versionId = [version].id) as StreetLetter
,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
    pt.id as propertyId
        FROM [dbo].[umbracoContent] 
        Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
        Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
        where pt.Alias = 'floor') And versionId = [version].id) as [Floor]
,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
    pt.id as propertyId
        FROM [dbo].[umbracoContent] 
        Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
        Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
        where pt.Alias = 'door') And versionId = [version].id) as Door
,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
    pt.id as propertyId
        FROM [dbo].[umbracoContent] 
        Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
        Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
        where pt.Alias = 'cOName') And versionId = [version].id) as COName
,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
    pt.id as propertyId
        FROM [dbo].[umbracoContent] 
        Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
        Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
        where pt.Alias = 'postalCode') And versionId = [version].id) as PostalCode
,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
    pt.id as propertyId
        FROM [dbo].[umbracoContent] 
        Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
        Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
        where pt.Alias = 'city') And versionId = [version].id) as City
,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
    pt.id as propertyId
        FROM [dbo].[umbracoContent] 
        Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
        Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
        where pt.Alias = 'country') And versionId = [version].id) as Country
,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
    pt.id as propertyId
        FROM [dbo].[umbracoContent] 
        Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
        Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
        where pt.Alias = 'attetion') And versionId = [version].id) as Attetion
, mg.MemberGroup as GroupId
,(Select [text] from [dbo].[umbracoNode] where id = mg.MemberGroup) as GroupName
,(SELECT TOP (1) [intValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
    pt.id as propertyId
        FROM [dbo].[umbracoContent] 
        Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
        Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
        where pt.Alias = 'nEWSLETTER') And versionId = [version].id) as Marketing
FROM [dbo].[cmsMember]
Left JOIN [dbo].[cmsMember2MemberGroup] as mg ON [dbo].[cmsMember].nodeId = mg.Member  
Left JOIN [dbo].[cmsMember] as m ON [dbo].[cmsMember].nodeId = m.nodeId
Left JOIN [dbo].[umbracoContentVersion]  as [version] On [dbo].[cmsMember].nodeId = [version].nodeId
Left JOIN [dbo].[umbracoNode]  as [Node] On [dbo].[cmsMember].nodeId = [Node].id

is working on a reply...