Counting documents in Alfresco’s database

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.

[sourcecode language=’sql’]
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
;
[/sourcecode]

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