Example: Determine 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 SQL and enterprise geodatabases, you must create versioned 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 versioned 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.

Note:

Not all the feature classes returned by these queries should be edited using SQL, even when using a versioned 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;