Fred's blog

Here is a rough and raw SQL procedure to count the documents in Alfresco 3.2, per type.
Of course, change your database name and type correct document types.

create procedure alfresco_doc_report ()
begin
    declare alfdoc_type varchar(50);
    declare loopdone int default 0;
    declare c1 cursor for select local_name from alf_qname where local_name in
                        ('doctype1', 'doctype2', 'doctype3');
    declare continue handler for NOT FOUND set loopdone = 1;

    open c1;

    fetch c1 into alfdoc_type;

    while not loopdone do
        SELECT alfdoc_type, count(*) FROM alfresco_rm.alf_node
        where store_id = (select id from alf_store where protocol = 'workspace' and identifier = 'SpacesStore')
        AND type_qname_id = (select id from alf_qname where local_name = alfdoc_type);

        fetch c1 into alfdoc_type;
    end while;

    close c1;

end
;

Edit: Removed the line ” inner join alfresco_rm.alf_node_properties b on a.id=b.node_id”