How to identify branch versioned feature classes from SDE Business Tables?

[AndrewRudin1]

I'm looking for a way to easily detect all feature classes in an enterprise geodatabase that are configured with branch versioning.  Anyone found a straightforward way to do this in an automated fashion?

Arcpy's describe provides a way to determine if the feature class is versioned, but no way to distinguish between traditional vs branch. So that's led me to look at the SDE business tables, and I found two possible candidates.  I'm curious if anyone has had more experience with these since I can't find documentation on what each do.  

BRANCH_TABLES_MODIFIED  is the first candidate. It has a BRANCH_ID column with many of the rows having a zero in this column. Then there is a REGISTRATION_ID which seems to be the same ID as on SDE.TABLE_REGISTRY.  I assume that BRANCH_ID = 0 equates to default version for each table.  So if I filter to BRANCH_ID=0 and join to TABLE_REGISTRY does that get me the comprehensive list?

MULTIBRANCH_TABLES is another candidate with a REGISTRATION_ID and a START_MOMENT date column.  I seem to get fewer tables back from this.

[DavidSolari]

From the SQL end, any registered table that's branch versioned will have some special fields to track the branch version info. In my environment the GDB_IS_DELETE field is a solid indicator, your EGDB configuration may create different fields. I'm not a SQL wizard but if you can get the schema for every table programmatically that should do you.

[MobiusSnake]

The XML definitions in the GDB_ITEMS table might have something?  (I don't actually know, but it's probably worth a look.)

[MarceloMarques]

SQL access to enterprise geodatabase data—ArcMap | Documentation (arcgis.com)

Geodatabase system tables—ArcMap | Documentation (arcgis.com)

Example: Determine which datasets are versioned in a geodatabase—ArcMap | Documentation (arcgis.com)

SQL SERVER
SELECT *
FROM sde.GDB_ITEMS
GO
--Versioned
SELECT NAME AS "Versioned feature class", definition
FROM sde.GDB_ITEMS
WHERE Definition.exist('(/*/Versioned)[1]') = 1
AND Definition.value('(/*/Versioned)[1]', 'nvarchar(4)') = 'true'
GO
--Not Versioned
SELECT NAME AS "Versioned feature class", definition
FROM sde.GDB_ITEMS
WHERE
Definition.exist('(/*/Versioned)[1]') = 1
AND Definition.value('(/*/Versioned)[1]', 'nvarchar(4)') = 'false'
GO
--Branch Versioned
SELECT NAME AS "Versioned feature class", definition
FROM sde.GDB_ITEMS
WHERE
Definition.exist('(/*/Versioned)[1]') = 1
AND Definition.value('(/*/Versioned)[1]', 'nvarchar(4)') = 'true'
AND Definition.exist('(/*/IsBranch)[1]') = 1
AND Definition.value('(/*/IsBranch)[1]', 'nvarchar(4)') = 'true'
GO
--Traditional Versioning
SELECT NAME AS "Versioned feature class", definition
FROM sde.GDB_ITEMS
WHERE
Definition.exist('(/*/Versioned)[1]') = 1
AND Definition.value('(/*/Versioned)[1]', 'nvarchar(4)') = 'true'
AND Definition.exist('(/*/IsBranch)[1]') = 1
AND Definition.value('(/*/IsBranch)[1]', 'nvarchar(4)') = 'false'
GO