[SOLVED] Sharepoint: SQL to find all documents created/edited by a user

Sharepoint: SQL to find all documents created/edited by a user

I am looking for a query that will work on Sharepoint 2003 to show me all the documents created/touched by a given userID.

I have found tables with the documents (Docs) and tables for users (UserInfo, UserData) but the relationship between seems a bit odd - there are 99,000 records in our userdata table, and 12,000 records in userinfo - we have 400 users!

I suppose I was expecting a simple 1 to many relationship with a user table having 400 records and joining that to the documents table, but I see thats not the case.

Any help would be appreciated.

Edit: Thanks Bjorn, I have translated that query back to the Sharepoint 2003 structure:

select 
d.* from 
userinfo u join userdata d 
on u.tp_siteid = d.tp_siteid  
and 
u.tp_id = d.tp_author 
where
u.tp_login = 'userid' 
and
d.tp_iscurrent = 1

This gets me a list of siteid/listid/tp_id's I'll have to see if I can trace those back to a filename / path. All: any additional help is still appreciated!

Solution

I've never looked at the database in SharePoint 2003, but in 2007 UserInfo is connected to Sites, which means that every user has a row in UserInfo for each site collection (or the equivalent 2003 concept). So to identify what a user does you need both the site id and the user's id within that site. In 2007, I would begin with something like this:

select d.* from userinfo u 
join alluserdata d on u.tp_siteid = d.tp_siteid 
and u.tp_id = d.tp_author 
where u.tp_login = '[username]'
and d.tp_iscurrentversion = 1

Update: As others write here, it is not recommended to go directly into the SharePoint database, but I would say use your head and be careful. Updates are an all-caps no-no, but selects depends on the context.