Example: Determining which datasets are versioned in a geodatabase
You can query the definition column of the GDB_Items table (or GDB_Items_vw view in Oracle) to return a list of feature classes that have the versioned value set to true (or 1, depending on the database).
As mentioned in A quick tour of using SQL with ArcSDE geodatabases, you must create multiversioned views to edit versioned data using SQL. Therefore, it would be useful to determine which feature classes in a geodatabase are versioned so you know whether you must create multiversioned views of a feature class before editing it with SQL.
The following example queries return a list of all versioned feature classes in the geodatabase on which the statement was executed.

Not all the feature classes returned by these queries should be edited using SQL, even when using a multiversioned view. See What type of data can be edited using SQL? for more information.
Be sure to connect to the correct database before executing this query.
--Queries PostgreSQL --Returns a list of versioned datasets in the specified geodatabase SELECT name AS "Versioned feature class", FROM sde.gdb_items WHERE (xpath('//Versioned/text()', definition))[1]::text = 'true';
--Queries a dbo-schema geodatabase in SQL Server --Returns a list of versioned datasets in the specified geodatabase SELECT NAME AS "Versioned feature class" FROM dbo.GDB_ITEMS WHERE Definition.exist('(/*/Versioned)[1]') = 1 AND Definition.value('(/*/Versioned)[1]', 'nvarchar(4)') = 'true'
--Queries Oracle --Returns a list of versioned datasets in the specified geodatabase SELECT items.name AS Dataset, itemtypes.name AS Dataset_Type FROM sde.gdb_items_vw items, sde.gdb_itemtypes itemtypes WHERE items.definition LIKE '%Versioned>true%' AND items.type = itemtypes.uuid;