topic Re: SDE - SQL where is Dataset info stored in Geodatabase Questions

SDE - SQL where is Dataset info stored

DaveOrlando — Fri, 10 Apr 2015 18:12:33 GMT

Hello,

I'm trying to track down a table that stores the Dataset info.

following this post, How to determine current edit session locking a version? ,

I'm trying something similar, but want to trace the tables back to their Dataset. How can I add to this query to do that? I can't seem to find Dataset info anywhere in the sql tables.

SELECT

v.name as version_name,

pri.owner as data_owner,

tr.table_name,

tl.*

FROM sde.sde_versions v

JOIN sde.SDE_state_locks sl ON sl.state_id = v.state_id

JOIN sde.SDE_process_information pri ON pri.sde_id = sl.sde_id

JOIN sde.sde_table_locks tl ON tl.sde_id = pri.sde_id

JOIN sde.sde_table_registry tr ON tr.registration_id = tl.registration_id

order by lock_time desc

Thanks,

Dave

Re: SDE - SQL where is Dataset info stored

SamuelTompsett — Fri, 10 Apr 2015 18:35:55 GMT

Hi Dave,

You can find the Feature Dataset information from the "GDB_" tables. You will have to do a few table joins to get the wanted information.

ArcGIS Help 10.1

Hopefully that helps!

-Sam

Re: SDE - SQL where is Dataset info stored

SamuelTompsett — Fri, 10 Apr 2015 18:49:12 GMT

It would be this link for SQL Server:

ArcGIS Help 10.1

Re: SDE - SQL where is Dataset info stored

DaveOrlando — Fri, 10 Apr 2015 22:18:29 GMT

Thanks,

Here is the query I wrote to list Feature Classes and their associated Datasets.

select items_a.Name as FeatureClass, items_b.Name as DataSet

from sde.GDB_ITEMS items_a

join sde.GDB_ITEMRELATIONSHIPS rels on rels.DestID = items_a.UUID

join sde.GDB_ITEMS items_b on items_b.UUID = rels.OriginID

join sde.GDB_ITEMTYPES types_a on types_a.UUID = items_a.Type

join sde.GDB_ITEMTYPES types_b on types_b.UUID = items_b.Type

where types_a.Name = 'Feature Class' and types_b.Name = 'Feature Dataset'

order by DataSet, FeatureClass

now I have to combine it with the query at the top to just list the data that is causing locks.