Provide tool to easily remove the field GLOBALID from feature class.

NickBos1

There are various approaches to remove the field GLOBALID from a feature class, but this should be something out of the box that takes care of this need. For those that are quite familiar of the hassle of the lack of current functionality for removal of this field please help by voting to promote this enhancement request.

The below snapshot depicts current esri context menu interface functionality to add Global IDs to a feature dataset's feature classes via a right mouse click on a feature dataset.

The below snapshot portrays what esri could do as one option to help ease the problem to remove the field GLOBALID.

Also request esri enhance their ArcToolbox --> Data Management Tools --> Fields --> Delete Field tool to allow deletion of the field/column of GLOBALID. In turn this would make the software more robust in model builder and python.

If there is an issue that esri doesn't allow the field to be dropped once created thinking it is still part of versioning, replication or related then esri should likewise enhance their product to unregister as versioned as a preliminary step.

We don't want workaround solutions anymore of VB code, ArcObjects when we don't have an ArcObjects specialist on staff, don't want users have to delete from inside MS Access let alone run a script from within MS Access to remove fields nor do we want to use the Feature Class to Feature Class Tool when we just want to work with the source data. We just want that straight forward solution that is already nearly at our fingertips per above request.

Thanks for your read and consideration.

Nick

RonnieRichards · ‎09-08-2013

Agree with the author we do not want to create or find work-arounds for VBA work-a-arounds that are no longer supported.

We experienced this issue for over 40 datasets and it was rather frustrating referencing the Technical Article. http://support.esri.com/fr/knowledgebase/techarticles/detail/33663

Who wants to deal with 1 feature class at a time?

Had to create SQL procedures to automate this as it should be out of the box using the ArcGIS tools. For those that need to automate here was our procedure:

Backup GDB

Drop all Indexes on all owned schema tables for Global ID

Drop all constraints on all owned schema tables for Global ID

Drop Global ID on all owned schema tables

Remove Global ID from SDE column registry

Test your versioned environment

obviously this is a hack but the primary reason a tool is needed that is not 1 feature class at a time.

AndriusBogvila · ‎11-11-2019

We need geoprocessing tool to delete GlobalIds from feature class. We use arcpy and geoprocessing tools to create database migration scripts. Without a proper way to delete GlobalIds we cannot rollback our database changes after GlobalIds are added.

Nicolas_GREHANT_IGNFI · ‎12-05-2022

Hi all.

KISS. Keep It Simple Stupid: it's very easy to enable Global ID and Editor Tracking through Arcpy, as well as to disable Editor Tracking thanks to Arcpy. But NOTHING for Disable Global ID...!

The only way to get rid of it: transfer from a GDB with GID to the same without GID (yes I know, quick to be done thanks to Load Dta and a good XCross, but that means "Production Mapping" extension (which we fortunately have) for something basic...

Hard for me to tell all my French and international partners to use Data Reviewer if it is so hard just to get rid of Global ID to go back to their original GDB structure (meaning without GID and Editor Tracking fields that are compulsory for most Data Reviewer attribute rules...)

Best regards, keep on getting the best!

GIS_Spellblade · ‎09-07-2023

So I was doing some work with an end-user and after troubleshooting something to deal with GlobalIDs, I said, no worries just use the search bar and type in: Remove GlobalIDs

Surprise. Surprise. This ArcGIS Idea from 2012 still valid and relevant 11 years later.

There needs to be an easier way to remove a GlobalID that does not require recreating a feature class.

PaulLohr · ‎09-22-2023

Great tip, @RonnieRichards. It would be great if you could share the SQL that you used to delete the Global ID column in your RDBMS.

11 years and still no fix for this? I'm quite weary of the way ESRI treats us.

RonnieRichards · ‎09-29-2023

@PaulLohr this was a long time ago but I do have my scripts. This idea does still have merit.

I will post my code blocks below which were quick and dirty with the intent to remove ALL global IDs. As-is they are non destructive and only generate the T-SQL statements to the console so they can be reviewed and executed in another session.

Not sure I would use this today with all of the functionality now available in the EGDB putting more dependencies on them such as archiving, attachment tables, versioned views, sync. This was before versioned views (_evws) were created by default and pretty sure they will not auto-update without an ArcGIS field event. These might require manual alterations if the global id fields did get dropped. Please use caution this could destroy a gdb with versioning, relationship classes, replicas, etc... They also might need some protections to not target an SDE or DBO owned schema objects.

Drop Indexes

DECLARE @VSQL NVARCHAR(2000)
DECLARE @CURR CURSOR
SET @CURR = CURSOR FAST_FORWARD FOR

SELECT 
'DROP INDEX ' + NAME +  ' ON '+OBJECT_SCHEMA_NAME(OBJECT_ID)+'.'+OBJECT_NAME(OBJECT_ID)+';'
FROM	SYS.INDEXES
WHERE	TYPE > 0
AND OBJECT_SCHEMA_NAME(OBJECT_ID) = 'GIS' --The Data Owner
AND NAME LIKE 'UUID%'
ORDER BY
OBJECT_SCHEMA_NAME(OBJECT_ID), 
OBJECT_NAME(OBJECT_ID)

OPEN @CURR
	FETCH NEXT FROM @CURR INTO @VSQL
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
	PRINT @VSQL
	FETCH NEXT FROM @CURR INTO @VSQL
	END
CLOSE @CURR;
DEALLOCATE @CURR;

Drop Constraints

DECLARE @VSQL NVARCHAR(2000)
DECLARE @CURR CURSOR
SET @CURR = CURSOR FAST_FORWARD FOR

SELECT 
'ALTER TABLE ' + SCHEMA_NAME(SCHEMA_ID) + '.' + OBJECT_NAME(PARENT_OBJECT_ID) + ' DROP CONSTRAINT ' + OBJECT_NAME(OBJECT_ID)
FROM SYS.OBJECTS
WHERE TYPE_DESC LIKE '%CONSTRAINT'
AND OBJECT_NAME(OBJECT_ID) = 'GLOBALID'	

OPEN @CURR
	FETCH NEXT FROM @CURR INTO @VSQL
	WHILE (@@FETCH_STATUS = 0)
	BEGIN	
	PRINT @VSQL
	FETCH NEXT FROM @CURR INTO @VSQL
	END
CLOSE @CURR;
DEALLOCATE @CURR;

Drop GlobalID

DECLARE @VSQL NVARCHAR(2000)
DECLARE @CURR CURSOR
SET @CURR = CURSOR FAST_FORWARD FOR

SELECT 
'ALTER TABLE ' + C.TABLE_SCHEMA + '.' + C.TABLE_NAME + ' DROP COLUMN ' + C.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
LEFT OUTER JOIN SDE.SDE_LAYERS L
ON L.TABLE_NAME = C.TABLE_NAME
AND L.OWNER = C.TABLE_SCHEMA
LEFT OUTER JOIN SDE.SDE_TABLE_REGISTRY R
ON L.TABLE_NAME = R.TABLE_NAME
LEFT OUTER JOIN SDE.SDE_COLUMN_REGISTRY CR
ON L.TABLE_NAME = CR.TABLE_NAME
AND L.OWNER = CR.OWNER
AND CR.COLUMN_NAME = 'GLOBALID'
LEFT OUTER JOIN INFORMATION_SCHEMA.VIEWS V
ON C.TABLE_NAME = V.TABLE_NAME
AND C.TABLE_SCHEMA = V.TABLE_SCHEMA
WHERE C.COLUMN_NAME = 'GLOBALID'
--AND (L.BASE_LAYER_ID = 0 OR L.BASE_LAYER_ID IS NULL)
AND V.TABLE_NAME IS NULL
ORDER BY L.TABLE_NAME

OPEN @CURR
	FETCH NEXT FROM @CURR INTO @VSQL
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
	PRINT @VSQL
	FETCH NEXT FROM @CURR INTO @VSQL
	END
CLOSE @CURR;
DEALLOCATE @CURR;

Drop GlobalID from the SDE Column Registry (be careful!!)

DECLARE @VSQL NVARCHAR(2000)
DECLARE @CURR CURSOR
SET @CURR = CURSOR FAST_FORWARD FOR

SELECT 
'DELETE FROM SDE.SDE_COLUMN_REGISTRY WHERE COLUMN_NAME = ''' + CR.COLUMN_NAME + ''' AND OWNER = ''GIS'' AND TABLE_NAME = ''' + '' + L.TABLE_NAME + ''''
FROM SDE.SDE_LAYERS L
INNER JOIN SDE.SDE_TABLE_REGISTRY R
ON L.TABLE_NAME = R.TABLE_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS C
ON L.TABLE_NAME = C.TABLE_NAME
AND C.COLUMN_NAME = 'GLOBALID'
AND L.OWNER = C.TABLE_SCHEMA
LEFT OUTER JOIN SDE.SDE_COLUMN_REGISTRY CR
ON L.TABLE_NAME = CR.TABLE_NAME
AND L.OWNER = CR.OWNER
AND CR.COLUMN_NAME = 'GLOBALID'
WHERE CR.COLUMN_NAME = 'GLOBALID'
ORDER BY L.TABLE_NAME

OPEN @CURR
	FETCH NEXT FROM @CURR INTO @VSQL
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
	PRINT @VSQL
	FETCH NEXT FROM @CURR INTO @VSQL
	END
CLOSE @CURR;
DEALLOCATE @CURR;

Hope this helps and good luck!

Ronnie

SSWoodward · ‎10-06-2023

Thank you everyone for your comments.

@RonnieRichards is correct that ArcGIS Pro uses Global IDs for a myriad of purposes, and that removing them after they have been applied could cause unexpected and undesirable results depending on your data. Since Global Ids are a form of row identification, in many cases removing them would be similar to removing the object ID from your data.

If possible, could some of the commenters share a bit more about what workflows they are performing where Global IDs must be removed after applying them?

SSWoodward · ‎10-06-2023

AllanBenvin_yyc · ‎12-08-2023

What would be useful is a tool to re-create the index on the globalid correctly for the state of the data. I find the preserveGlobalIDs setting to be key to certain operations like append but depending on the version of ArcGIS that you used when you created the GlobalID column the indexes may not qualify for this setting to work and may cause more problems.

The article here describes the indexes for GlobalID on base, A, D, and archive tables depending if your data is versioned, archived etc. FAQ: How Have Unique Indexes on Geodatabase-Managed Global IDs Changed? (esri.com)

We have thousands of datasets created over the years and the indexes are in all sorts of different states. Fixing these up based on the rules in the link is pretty much a nightmare.

SSWoodward · ‎12-11-2023

Thanks for the idea @AllanBenvin_yyc. I encourage you to open an Idea specific to your request so that it can receive kudos and build support within the community.