Useful DQL queries

DQL to get all empty (sub)folders in a cabinet:
select * from dm_folder where r_link_cnt=0 and folder('/Temp',descend)

DQL to get list of documents and their folder path:
select distinct d.r_object_id,d.object_name,f.r_folder_path from dm_document d, dm_folder f where any d.i_folder_id=f.r_object_id and r_folder_path is not nullstring enable(ROW_BASED)

DQL to display the supertypes hierarchy brach of the specified type:

select r_supertype from dmi_type_info where r_type_id = (select r_object_id from dm_type where name='my_type')

DQL to get number of modified documents for each month:
select datetostring(r_modify_date,'mm/yyyy'),count(*)from dm_document [WHERE condition] group by datetostring(r_modify_date,'mm/yyyy')

DQL to execute an SQL query:
execute exec_sql with query = 'create or replace my_view (cod) as (select some_id from my_table)'

DQL to get the object type of a document:
select r_object_type from dm_document where r_object_id='092e6adc800001f0'

DQL to get the number of sysobjects for each object type:
select count(*),r_object_type from dm_sysobject group by r_object_type

DQL to create a DB index on a type attribute:
EXECUTE make_index WITH type_name='dmi_workitem',attribute='r_workflow_id'

DQL to see Documentum sessions on current Content Server:
execute show_sessions

DQL to get ids of documents deleted in a time interval:
select * from dm_audittrail where event_name='dm_destroy' where time_stamp > date('date before') and time_stamp < date('date after')

DQL to get the user that deleted a document:
select * from dm_user where r_object_id= (select user_id from dm_audittrail where event_name='dm_destroy' and audited_obj_id='ID OF DELETED OBJECT')