topic Missing GDB tables in SQL 2008 enterprise database in Data Management Questions

Missing GDB tables in SQL 2008 enterprise database

LauraSilsbee1 — Mon, 17 Jun 2013 18:22:17 GMT

We are running ArcSDE 10.1 on SQL 2008 R2, and created the databases through the Geodatabase Administration tools. I am trying to access the GDB_DOMAINS table and it does not exist for any of our databases created this way. The only place that I can find info about a domain is in the .xml link in the entry in the GDB_ITEMS table. I need to change ownership of a domain and have been unable to accomplish that through the Geodatabase Administration tools.

The only GDB tables we have are GDB_TABLES_LAST_MODIFIED, GDB_ITEMS, GDB_ITEMRELATIONSHIPS, GDB_ITEMTYPES, GDB_ITEMRELATIONSHIPTYPES, AND GDB_REPLICALOG.

Thanks,
Laura

Re: Missing GDB tables in SQL 2008 enterprise database

AsrujitSengupta — Mon, 17 Jun 2013 18:47:53 GMT

Laura,

The number of GDB tables in a 10.1 sde geodatabase has reduced to 6.

Have a look at this link for the detailed information on these tables:

System tables of a geodatabase in SQL Server
http://resources.arcgis.com/en/help/main/10.1/index.html#//002q00000080000000

GDB_ITEMS
Items are any object used in the ArcGIS system that can be indexed and searched, including tables, domains, topologies, and networks. The GDB_ITEMS table maintains information about all the items stored in the geodatabase.

Hope this helps!

Regards,

Re: Missing GDB tables in SQL 2008 enterprise database

LauraSilsbee1 — Mon, 17 Jun 2013 19:06:12 GMT

That does help quite a bit - thank you. Since I am unable to change the domain ownership through ArcGIS I am going to have to do it through SQL - any recommendations on how to proceed or warnings on what to avoid?

Thanks,
Laura

Re: Missing GDB tables in SQL 2008 enterprise database

AsrujitSengupta — Mon, 17 Jun 2013 19:40:45 GMT

Laura,

The Domain ownership can be changed by using the following workaround:
1. Export 'Domains' owned by 'User1' to tables using 'Domain to Table' tool
2. Import/Create 'Domains' from the tables created in step 1 using 'Table to Domain' tool connected as the new owner
3. Remove original 'Domains' owned by 'User1' from the field
4. Assign newly created 'Domains' in step 2 to fields from which they were removed in step 3
5. Delete the 'Domains' owned by 'User1'

Finding domain owners using SQL:
http://resources.arcgis.com/en/help/main/10.1/index.html#//006z000000ts000000

--SQL Server
SELECT items.Name AS "Domain Name",
items.Definition.value('(/*/Owner)[1]','nvarchar(max)') AS "Owner"
FROM dbo.GDB_ITEMS AS items INNER JOIN dbo.GDB_ITEMTYPES AS itemtypes
ON items.Type = itemtypes.UUID
WHERE itemtypes.Name IN ('Coded Value Domain', 'Range Domain')

There are actually some Ideas submitted for these purposes, which you can have a look at:

Domain Management Toolset:
http://esri.force.com/ideaView?id=087300000008Hzc&returnUrl=%2Fapex%2FideaList%3Fc%3D09a300000004xET%26category%3DGeodatabase

Add Change Domain Owner Tool:
http://ideas.arcgis.com/ideaView?id=08730000000c1vc

Regards,

Re: Missing GDB tables in SQL 2008 enterprise database

AsrujitSengupta — Mon, 17 Jun 2013 19:51:09 GMT

Make sure you have the recent database backup before trying anything...:)

Re: Missing GDB tables in SQL 2008 enterprise database

LauraSilsbee1 — Mon, 17 Jun 2013 19:57:36 GMT

asrujit;306865 wrote:
Laura,

The Domain ownership can be changed by using the following workaround:
1. Export 'Domains' owned by 'User1' to tables using 'Domain to Table' tool
2. Import/Create 'Domains' from the tables created in step 1 using 'Table to Domain' tool connected as the new owner
3. Remove original 'Domains' owned by 'User1' from the field
4. Assign newly created 'Domains' in step 2 to fields from which they were removed in step 3
5. Delete the 'Domains' owned by 'User1'

Unfortunately this isn't working - I'm still getting an "Must be the owner to perform this operation" when I try the Table to Domain tool. Also, I don't know which fields in which tables have this domain attached - this user does not own any tables. I'm still sorting through how to figure that out.

Thanks,
Laura