Warning: Do this only if you don't have any other choice!
So I got to a stage in my code where SQL Server Management shown:
And my test showed the following error:
ERROR: Table already registered [Microsoft SQL Server Native Client 10.0: Violation of UNIQUE KEY constraint 'registry_uk2'. Cannot insert duplicate key in object 'sde.SDE_table_registry'. The duplicate key value is (CARS, GIS, FLUENTMIGRATOR).] [FluentMigrator.GIS.CARS]
What does it mean?
It means I deleted a table with "DROP TABLE" where I needed to use ArcObjects/ArcCatalog and all I got from it is this lousy T shirt… Just without the T shirt…
So it should come as no surprise that SDE stores the metadata of their feature/table layers, and when deleting tables they get deleted from it.
The SDE Database table structure:
(At least on my computer)
From the Exception table - SDE_table_registry contains:
See line 7 – that’s our CARS non layer table
Lets delete those rows.
But don't think we are done, there is still the column registry table - SDE_column_registry:
Lets delete those rows as well.
For feature Layers the situation is a bit more complex:
For example we look at REGIONS table:
At SDE_table_registry:
At SDE_column_registry:
At SDE_layers:
At SDE_geometry_columns:
(Don't worry we are not yet done!)
At GDB_ITEMRELATIONSHIPS if we query:
SELECT sde.GDB_ITEMS.*, sde.GDB_ITEMRELATIONSHIPS.*
FROM sde.GDB_ITEMRELATIONSHIPS INNER JOIN
sde.GDB_ITEMS ON sde.GDB_ITEMRELATIONSHIPS.DestID = sde.GDB_ITEMS.UUID
we get some items from GDB_ITEMS (see next item).
At GDB_ITEMS:
And
And we must not forget the SPs – in our database we need to delete the SPs whose number is not used by any table (see SDE – A look at the layer table structure).
And at least according to the profiler when delete another feature layer be sure to look at SDE_metadata.
To Sum it all together – SDE sucks!
But now here is a script I wrote (I am not a DBA so there might be mistakes) that does all this deletes (be sure to replace all the grey matter):
declare @LayerId int
declare @SdeLockId int
declare @DatabaseName varchar(32)
declare @TableName varchar(128)
declare @OwnerName varchar(32)
set @DatabaseName = 'FLUENTMIGRATOR'
set @TableName = 'REGIONS'
set @OwnerName = 'GIS'
--Getting the layer id
Select @LayerId=layer_id
from sde.sde.SDE_layers
where database_name=@DatabaseName and table_name=@TableName and owner=@OwnerName
--Getting the lock id
Select @SdeLockId= sde_id
from sde.sde.SDE_layer_locks
where layer_id=@LayerId
DELETE FROM sde.GDB_ITEMRELATIONSHIPS
WHERE DestID in (SELECT UUID FROM sde.GDB_ITEMS WHERE Name = 'FluentMigrator.GIS.REGIONS')
DELETE FROM sde.GDB_ITEMS WHERE Name = 'FluentMigrator.GIS.REGIONS'
DELETE FROM sde.SDE_column_registry WHERE owner=@OwnerName and table_name=@TableName and database_name=@DatabaseName
DELETE FROM sde.SDE_geometry_columns WHERE f_table_catalog=@DatabaseName and f_table_schema=@OwnerName and f_table_name = @TableName
DELETE FROM sde.SDE_layers WHERE database_name=@DatabaseName and owner=@OwnerName and table_name = @TableName
DELETE FROM sde.SDE_table_registry WHERE database_name=@DatabaseName and owner=@OwnerName and table_name = @TableName
exec sde.sde.SDE_layer_def_delete @LayerId
exec sde.sde.SDE_geocol_def_delete @DatabaseName,@OwnerName,@TableName,N'SHAPE'
exec sde.sde.SDE_layer_lock_def_delete @SdeLockId,@LayerId,'N'
exec sde.sde.SDE_layer_lock_def_delete_user @SdeLockId
exec sde.sde.SDE_state_lock_def_delete_user @SdeLockId
exec sde.sde.SDE_table_lock_def_delete_user @SdeLockId
exec sde.sde.SDE_object_lock_def_delete_user @SdeLockId
exec sde.sde.SDE_pinfo_def_delete @SdeLockId
This seems to work for both regular tables and feature layers.
If someone has a better suggestions for this script please let me know.
