Step by step approach to running sql queries on Alfresco DB

[echiboka]

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.

[kaynezhang]

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')

[echiboka]

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.

[afaust · Accepted solution]

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