Delete geodatabase tables within a specific schema from a RDBMS, when no data is visible for the specific schema through ArcGIS applications

Summary

Often after deletion of complex data models like geometric network, trace network, parcel fabric, or utility network from ArcGIS client applications (ArcGIS Pro/ArcMap) under a specific schema, we could still observe the associated system tables related and entries in the geodatabase core system tables from the RDBMS application. This means that the deletion of the data was incomplete. This might be because of application crash operations in the ArcGIS client while performing deletion or for various race conditions and system issues in client machines.

In that scenario, we would have to manually remove those from RDBMS and also their entries from the geodatabase core system tables.

Note:

• Before adhering to the solution, make sure a full geodatabase backup has been generated.
• Additionally, data under the specific schema is not present or has already been deleted from ArcGIS Pro/ArcMap.
• Note down the names for the tables and their schema owners that were orphaned.

Procedure

1. Deletion of the entries from the geodatabase core system tables related to tables owned by the redundant schema:
   • Run the following query to detect the tables under the specific schema

select * from dbo.GDB_ITEMS where name like '%<schema_name> %';
select * from dbo.SDE_TABLE_REGISTRY where owner like '%<schema_name>%';
select * from dbo.SDE_COLUMN_REGISTRY where owner like '%<schema_name>%';
select * from dbo.SDE_LAYERS where owner like '%<schema_name>  %';
select * from dbo.SDE_GEOMETRY_COLUMNS where f_table_schema like '%<schema_name> %';

• • If entries had been detected, delete the same using the below SQL queries:

Delete from dbo.GDB_ITEMS where name like '%<schema_name> %';
Delete from dbo.SDE_TABLE_REGISTRY where owner like '%<schema_name>%';
Delete from dbo.SDE_COLUMN_REGISTRY where owner like '%<schema_name>%';
Delete from dbo.SDE_LAYERS where owner like '%<schema_name>  %';
Delete from dbo.SDE_GEOMETRY_COLUMNS where f_table_schema like '%<schema_name> %';

2. Deletion of the tables owned by the redundant schema from the RDBMS itself:
   • The following query helps in the removal of multiple associated tables under a specific schema at once.

DECLARE @sql nvarchar(max) = '';

3. Build the DROP TABLE statements for all tables in <schema_name> schema:

SELECT @sql = @sql + 'DROP TABLE<schema_name>.' + QUOTENAME(t.name) + ';'
FROM sys.tables t
WHERE t.type = 'U' AND t.schema_id = SCHEMA_ID('<schema_name>');

4. Execute the constructed DROP TABLE statements

EXEC sp_executesql @sql;