SQL Query to show Documents that have Versions or Revisions

[Roger_Linhart]

Has anyone written a query or know what tables/columns indicate when a document has more than one version or revision?

[Larissa_Armand]

Hi Roger, 

I would start with REVNOTETABLE. 

[Scott_Johnson3]

Hi All

A simpler approach is using itemdata. Look a the column maxdocrev. If the value is > 0 the there are revisions.

Good Luck

[Paolo_DiLernia · Accepted solution]

 @Roger Linhart I agree with @Scott Johnson I found this query on this link a while back. 

From <https://community.hyland.com/forum/threads/53006-find-documents-with-1-revision>

SELECT

hsi.itemdata.itemnum,

hsi.itemdata.itemname,

hsi.itemdata.itemtypegroupnum,

hsi.itemtypegroup.itemtypegroupname,

hsi.itemdata.itemtypenum,

hsi.doctype.itemtypename,

hsi.itemdata.maxdocrev,

hsi.itemdata.maxdocrev + 1 AS [RevNbr]

FROM

hsi.itemdata

INNER JOIN hsi.itemtypegroup ON hsi.itemdata.itemtypegroupnum = hsi.itemtypegroup.itemtypegroupnum

INNER JOIN hsi.doctype ON hsi.itemdata.itemtypenum = hsi.doctype.itemtypenum

WHERE

(maxdocrev <> 0)

ORDER BY

itemtypegroupnum,

RevNbr

[Justyna_Jurgiel]

Hello, how to check in the database who and when performed the revision ?