1.List files and folder in specified folder path
SELECT DISTINCT s.object_name, fr.r_folder_path FROM dm_sysobject(ALL) s,dm_sysobject_r sr,dm_folder_r fr
WHERE sr.i_position = -1
AND sr.r_object_id = s.r_object_id
AND fr.r_object_id = sr.i_folder_id
AND fr.i_position = -1
AND fr.r_folder_path like ‘{0}%‘
ORDER BY fr.r_folder_path,s.object_name
2.List all child folders of a particular folder
SELECT object_name FROM dm_folder WHERE r_object_id IN (SELECT i_folder_id FROM dm_sysobject WHERE FOLDER(‘{0}‘,DESCEND) AND r_object_type = ‘dm_folder‘)
3.Get all parent folder paths
SELECT object_name FROM dm_folder WHERE r_object_id IN (SELECT i_ancestor_id FROM dm_folder WHERE r_object_id = ‘{0}‘)
4.Get all checked out documents with folder path
SELECT d.object_name, fr.r_folder_path,d.r_lock_owner
FROM dm_document d,dm_sysobject_r sr,dm_folder_r fr
WHERE d.i_is_deleted = 0 AND sr.i_folder_id = fr.r_object_id AND d.r_object_id = sr.r_object_id AND sr.i_position = -1 AND fr.i_position = -1
AND FOLDER(‘{0}‘,DESCEND) AND r_lock_owner = ‘{1}‘ ‘‘
5.Content Size in Store Summary
SELECT s.name, SUM(c.content_size * 1.00), AVG(c.content_size * 1.00), MAX(c.content_size)
FROM dmr_content c, dm_store s
WHERE c.storage_id = s.r_object_id AND s.store_type IN(1,2,3,5) And ANY parent_id IS NOT NULL
GROUP BY s.name
6.Get Largest files
SELECT r_object_id,object_name,r_content_size
FROM dm_document
WHERE Folder(‘{0}‘,
DESCEND)
ORDER BY r_content_size
DESC
ENABLE (RETURN_TOP 10)
7.List all objects in a filestore
SELECT * FROM dm_document(ALL) WHERE r_object_id IN
(
SELECT parent_id
FROM dmr_content ct,dm_filestore fs
WHERE fs.name=‘filestore_01‘
AND fs.r_object_id=ct.storage_id)
ENABLE (ROW_BASED)
EMC Documentum DQL整理（四）
原文：http://www.cnblogs.com/alex09/p/4372148.html
