Script to see who owns My Reports folder

[TheBrenda]

We changed some field names. Now we are running a script to identify all customer reports/queries that at the client site that must be changed to use the new names. the script (which i got from this site) give the report/query path. Out paths are setup to be Public Folders or My Folders, which are specific per user. If the path is My Folders, how do i know which My Folders? BTW - I am not an administrator, just assigned to building the script to identify report/queries that need to be changed. But wondering what good is saying that the path is My Folders when there is one per user.

Anyone has a script to see who owns the My Folders that contains a certain report?

[blom0344]

The problem is - as always - with the nested/recursive structure of the content store.  The following T-sql script  yields those reports stored in my folders, but not within a folder, but directly at the root level:

Code Select Expand

WITH MYFOLDERS AS (
SELECT A.CMID AS PCMID,PCMID AS OWNER
FROM CMOBJNAMES A INNER JOIN CMOBJECTS B ON A.CMID = B.CMID WHERE NAME LIKE 'My Folders%'
),MYOWNERS AS
(SELECT CMID AS OWNER, USERNAME FROM CMOBJPROPS1 WHERE USERNAME IS NOT NULL
),INDIVIDUAL_REPORTS_OR_FOLDERS AS
(SELECT 'REPORT' AS TYPE,NAMES.NAME AS REPORT,NAMES.LOCALEID,NAMES.CMID,PCMID AS PARENT
FROM CMOBJECTS
INNER JOIN CMOBJNAMES NAMES
ON CMOBJECTS.CMID = NAMES.CMID
WHERE CLASSID = (select classid from CMCLASSES WHERE name = 'report') AND DISABLED IS NULL AND
NAMES.LOCALEID = (select max(LOCALEID) from CMLOCALES where LOCALE IN ('en'))
union all
SELECT 'FOLDER' AS TYPE,NAMES.NAME AS REPORT,NAMES.LOCALEID,NAMES.CMID,PCMID AS PARENT
FROM CMOBJECTS
INNER JOIN CMOBJNAMES NAMES
ON CMOBJECTS.CMID = NAMES.CMID
WHERE CLASSID = (select classid from CMCLASSES WHERE name = 'folder') AND DISABLED IS NULL AND
NAMES.LOCALEID = (select max(LOCALEID) from CMLOCALES where LOCALE IN ('en'))
)SELECT * FROM
MYFOLDERS INNER JOIN INDIVIDUAL_REPORTS_OR_FOLDERS ON PCMID = PARENT
INNER JOIN MYOWNERS ON MYFOLDERS.OWNER = MYOWNERS.OWNER
ORDER BY PARENT


assuming the design language is  'en'

With folders stored within myfolders, the script would be a little bit more complicated