Core DB SQL script listing all non-deleted documents containing uploaded files

Document created by allanlewis67 on Apr 8, 2014Last modified by allanlewis67 on Apr 8, 2014
Version 3Show Document
  • View in full screen mode

Perhaps this will help someone.

 

/* lists the internalDocID (document number) for all documents containing uploaded files,
   excluding those that have been deleted   

 

JiveDocumentBody does not link directly to jiveDocument; it must go through JiveDocBodyVersion 
http://docs.jivesoftware.com/schemas/6_0/sbs/tables/jivedocument.html

The JiveDocumentBody table contains the actual documents in your system.

Has a uploaded binary:  jiveDocumentBody.fileName is not NULL

 

The jiveDocVersion table has a state column that indicates the current status of a doc
deleted
incomplete
published
archived
*/

 

use jivecore
GO

 

SELECT distinct d.internalDocID
  FROM jiveDocument D,
     jiveDocumentBody B,
     jiveDocBodyVersion BV,
     jiveDocVersion DV
  where
      d.internalDocID = BV.internalDocID
      and b.bodyID = bv.bodyID
   and d.internalDocID = dv.internalDocID
      and dv.state = 'published'  /*  an active document  */
  and B.fileName is not NULL  /* has a binary  */
  group by d.internalDocID
  order by d.internalDocID
GO

Attachments

    Outcomes