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 a
ll 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