topic Re: Remove row from SDE.Table_Registry in Data Management Questions

Remove row from SDE.Table_Registry

BlakeTerhune — Thu, 23 Jul 2015 18:02:15 GMT

Our SDE (10.0 on Oracle 11g) has a tabular view created from a feature class and a table. The view is valid in Toad but it does not show up in ArcCatalog for either the schema owner or the SDE user; arcpy.Exists() returns False. There is, however, an entry in the SDE.Table_Registry for the view. How can I remove this view from the table registry?

Re: Remove row from SDE.Table_Registry

SamuelTompsett — Thu, 23 Jul 2015 21:07:36 GMT

Hi Blake,

It would be best to contact ESRI Technical Support so they can verify the view is orphaned and can walk you through the process of removing all records in the repository tables.

-Sam

Re: Remove row from SDE.Table_Registry

BlakeTerhune — Thu, 23 Jul 2015 22:18:46 GMT

Thanks. I'll do that and report back.

Re: Remove row from SDE.Table_Registry

BlakeTerhune — Tue, 28 Jul 2015 16:29:00 GMT

I believe these orphaned table names in the Table_Registry were caused by dropping the table or view from Toad instead of ArcCatalog. After speaking with Esri support, they recommended cleaning this up by following these steps:

Identify "layer_id" for "table_name" in the SDE_LAYERS table
Identify "Registration_ID" for "table_name" in the SDE_TABLE_REGISTRY table
Identify "ID" for "Name" in the GDB_items table
Identify "g_table_name" for "f_table_name" in the SDE_GEOMETRY_COLUMNS table
Delete "f" and "s" tables for "g_table_name" found in Step 4. If "f" table is "f22" then "s" table is "s22".
Delete entry for "Name" in GDB_Items.
Delete entry for "f_table_name" in SDE_GEOMETRY_COLUMNS
Delete entry for "table_name" in SDE_LAYERS
Delete "i" table for "Registration_ID" (Step 4)
Remove all "table_name" entries in "SDE_COLUMN_REGISTRY".
Remove "table_name" entry in "SDE_TABLE_REGISTRY"

ArcGIS Help 10.2 - System tables of a geodatabase stored in Oracle

Basically, go through these six system tables and delete any rows that reference the object you are trying to delete.

SDE.LAYERS
SDE.TABLE_REGISTRY
SDE.COLUMN_REGISTRY
SDE.GDB_ITEMS
SDE.GEOMETRY_COLUMNS
SDE.COLUMN_REGISTRY

Re: Remove row from SDE.Table_Registry

RandyKreuziger — Fri, 31 Jul 2015 21:00:33 GMT

In SQL Server if one of the database owners deletes a Geodatabase registered table or view with the SQL tools the same things happens but the fix is much cleaner. Simply create a dummy table of the same name using the SQL tools (Management Studio) and then go in and delete the table/view using ArcCatalog.

Re: Remove row from SDE.Table_Registry

AsrujitSengupta — Fri, 31 Jul 2015 21:07:29 GMT

That is applicable for Oracle and other DBMSs as well. However, sometimes when that doesn't help...the above mentioned steps might be helpful.

Re: Remove row from SDE.Table_Registry

BlakeTerhune — Fri, 31 Jul 2015 23:18:14 GMT

The Esri support technician did try this though. It makes sense and would be a logical first step.

Re: Remove row from SDE.Table_Registry

Arne_Gelfert — Sat, 11 Dec 2021 07:10:43 GMT

A dusty thread but I still found this very helpful. While I recently completed one of the ESRI SDE classes to get more familiar with the data model, this was a good summary. I had one of those orphan table pop up in table_registry. As a check for others, I ran a quick::

select A.table_name, B.name
from SDE_table_registry A
left join
sys.tables B
on A.table_name = B.name 
where b.name is NULL‍‍‍‍‍‍

There can be any number of tables in your database (SQL Server in my case) but I'm thinking there shouldn't be anything in the registry tables that is not also in the system table.

Re: Remove row from SDE.Table_Registry

BlakeTerhune — Sat, 11 Dec 2021 07:10:46 GMT

Great idea Arne Gelfert‌. Here's the equivalent for us Oracle folks.

select A.table_name
from SDE.table_registry A
left outer join ALL_TABLES B
    on A.table_name = B.table_name
where b.table_name is NULL‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍