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

 

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