1.Get Content Size in folder
SELECT SUM(r_full_content_size/1024.0) FROM dm_sysobject WHERE FOLDER('{0}', DESCEND)
2.Count of documents in each folder/subfolder
SELECT fr.r_folder_path, COUNT(*) FROM dm_sysobject s, dm_sysobject_r sr, dm_folder_r fr, dm_document dm
WHERE s.i_is_deleted = 0 AND sr.i_position = -1 AND fr.i_position = -1 AND sr.r_object_id = s.r_object_id
AND fr.r_object_id = sr.i_folder_id AND dm.r_object_id = s.r_object_id AND FOLDER('{0}', DESCE
ND)
GROUP BY fr.r_folder_path
3.Get all types used in a specific folder
SELECT DISTINCT r_object_type FROM dm_sysobject WHERE FOLDER ('{0}', DESCEND)
4.Get Current Logged in users
EXECUTE list_sessions;
5.Get Retention
SELECT r_object_id,rendition,full_format,content_size,set_client,set_file,set_time,storage_id FROM dmr_content WHERE ANY parent_id ='{0}'
6.Get duplicate objects
SELECT object_name, COUNT(object_name) FROM dm_document GROUP BY object_name HAVING COUNT(object_name)>=2
7.Get file location
EXECUTE get_file_url FOR '{0}'
EXECUTE get_path FOR '{0}' (Id in dmr_content)
8.List empty folders by cabinet
SELECT f1.r_object_id as objectid, f1.object_name, f1.r_object_id, f1.r_folder_path
FROM dm_folder f1
WHERE ANY i_ancestor_id IN (SELECT r_object_id FROM dm_cabinet)
AND NOT EXISTS (SELECT f2.object_name FROM dm_sysobject(ALL) f2 WHERE ANY f2.i_folder_id = f1.r_object_id)
AND ANY f1.r_folder_path IS NOT NULLSTRING
FROM dm_folder f1
WHERE ANY i_ancestor_id IN (SELECT r_object_id FROM dm_cabinet)
AND NOT EXISTS (SELECT f2.object_name FROM dm_sysobject(ALL) f2 WHERE ANY f2.i_folder_id = f1.r_object_id)
AND ANY f1.r_folder_path IS NOT NULLSTRING
9.Get all documents checked out by a user
SELECT object_name FROM dm_document WHERE r_lock_owner = '{0}'
10.Content size by format
SELECT full_format,SUM(content_size * 1.00) , MAX(content_size) FROM dmr_content GROUP BY full_format ORDER BY full_format