Sharepoint: SQL to find all user created / edited documents
I am looking for a query that will work in Sharepoint 2003 to show me all documents created / affected by a given user ID.
I found tables with documents (Docs) and tables for users (UserInfo, UserData) but the relationship between them seems a little strange - our userdata table has 99,000 records and 12,000 records in userinfo - we have 400 users!
I guess I was expecting a simple 1 to many relationship with a user table having 400 records and joining it to the docs table, but I can see that this is not the case.
Any help would be appreciated.
Edit: Thanks Bjorn, I have translated this request back to the Sharepoint 2003 framework:
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 gives me a list of siteid / listid / tp_id. I need to see if I can trace them back to the filename / path. Everyone: Any additional help is still appreciated!
source to share
I've never looked at a database in SharePoint 2003, but in 2007 UserInfo is connected to sites, which means every user has a row in UserInfo for every site collection (or the 2003 equivalent concept). So, to determine which user you need both the site id and the user id on that site. In 2007, I would start 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 access the SharePoint database directly, but I would say use your head and be careful. Updates are all-no-no-headers, but the choice depends on the context.
source to share
If you are going to use this query in Sharepoint, you should know that creating views in the content database or querying directly to the database seems like a big No-No. The workaround could be some custom code that iterates through the object model and writes the results to your own database. It can be a timer or eventtrigger based.
source to share
DO NOT REQUEST A SHAREPOINT DIRECTLY BATTERY!
I wonder if I made it clear enough? :)
You really need to take a look at the object model available in C #, you will need to get the SPSite instance for the SiteCollection and then iterate over the SPList instances owned by SPSite and SPWeb objects.
Once you have an SPList object, you will need to call GetListItems using a query that filters for the user you want.
It is a supported way of doing what you want.
You should never access the database directly as SharePoint is not designed to do this at all, and there is no guarantee (in fact, there is a specific warning) that the database structure will be the same between versions and updates, and furthermore when content is spread across multiple databases content data in the farm, there is no guarantee that a query that runs against one content database will do what you expect from another content database.
When looking at the object model for iteration, also note that you will need to delete () the generated SPSite and SPWeb objects.
Oh yeah, you may have 400 users, but I bet you have 30 sites. The information is repeated in the database on the site ... 30 x 400 = 12,000 records in the database.
You really shouldn't be doing SELECTs with Locks, or adding WITH (NOLOCK) to your queries. Some parts of the system are very timeout sensitive, and if you start introducing locks that the system was not expecting, you can see the system go out of hand.
But really, you have to do it with the object model. Worrying with something like IronPython and experimenting with OM is almost completely enjoyable.
source to share