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 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;