topic Step by step approach to running sql queries on Alfresco DB in Alfresco Forum

Step by step approach to running sql queries on Alfresco DB

echiboka — Thu, 12 Jan 2017 09:35:07 GMT

Good day All,Please i am trying to find out the volume of documents on Alfresco at any given time besides generating other reports, can someone give me a step by step approach as to how and where i can run this sql queries on Alfresco Db?Look forward to your kind response.

Re: Step by step approach to running sql queries on Alfresco DB

kaynezhang — Thu, 12 Jan 2017 13:39:23 GMT

You can try to query all content's size by using following sql

SELECT sum(u.content_size)
FROM alf_node AS n,
alf_node_properties AS p,
alf_node_properties AS p1,
alf_namespace AS ns,
alf_qname AS q,
alf_content_data AS d,
alf_content_url AS u
WHERE n.id=p.node_id
AND ns.id=q.ns_id
AND p.qname_id=q.id
AND p.long_value=d.id
AND d.content_url_id=u.id
AND p1.node_id=n.id
AND p1.qname_id IN (SELECT id FROM alf_qname WHERE local_name='content')

Re: Step by step approach to running sql queries on Alfresco DB

echiboka — Thu, 12 Jan 2017 13:44:16 GMT

Hi Kanye,

Please where do i run this query?

Do i go to C:\Alfresco\postgresql\bin to run the query?

I need a step by step approach on where i can run this query on the Db.

Thanks.

Re: Step by step approach to running sql queries on Alfresco DB

afaust — Fri, 13 Jan 2017 07:45:43 GMT

Step 1: Start a DB client program (e.g. pgadmin or just psql)

Step 2: Connect to the database

Step 3: Open up a SQL query editor

Step 4: Query

Alternatively: simply right click on your contentstore folder, select "Properties" and let windows count the files + sizes for you