Getting a list of binaries and filesizes from the CM database

Sometimes you just need to get a report of the number of binaries in the CM database and their file-size, this SQL query will do just that.

 

SELECT IT.PUBLICATION_ID AS PublicationID,
       IT.ITEM_REFERENCE_ID AS ComponentID,
       IT.ITEM_TYPE  AS ItemType,
       IT.TITLE AS ComponentName,
       IT.DESCRIPTION AS ComponentDescription,
       IT.VERSION AS ActiveVersion,
       B.CONTENT_SIZE AS Size
FROM ITEMS IT,
     BINARIES B
WHERE IT.ITEM_TYPE = 16 AND
      IT.BINARY_ID = B.ID AND
B.CONTENT_SIZE > 1024 AND
      IT.VERSION = (SELECT MAX(VERSION) FROM ITEMS I2 WHERE I2.PUBLICATION_ID = IT.PUBLICATION_ID AND I2.ITEM_REFERENCE_ID = IT.ITEM_REFERENCE_ID)
order by  B.CONTENT_SIZE Desc

 

Usual disclaimer

Run as a read only user, not supported by SDL support etc.