topic Updating Field Alias Names Using SQL Management Studio ----> ArcSDE 10.x in Data Management Questions

Updating Field Alias Names Using SQL Management Studio ----> ArcSDE 10.x

deleted-user-74-8Fy1vCPqk — Mon, 17 Sep 2012 23:32:28 GMT

I see that field alias names in ArcSDE 10.x are in the GDB_Items table stuffed into an xml. Formerly in 9.x field aliases could be easily updated en masse in the GDB_FIELDINFO table. Anybody know the best/easiest/fastest way to update field aliases in 10.x using SQL Management Studio, if there is one?

Thanks

Dan Schall
Stowe, VT

Re: Updating Field Alias Names Using SQL Management Studio ----> ArcSDE 10.x

JackZHANG — Wed, 23 Jan 2013 22:43:11 GMT

Got same question here. have you sorted it out?

Re: Updating Field Alias Names Using SQL Management Studio ----> ArcSDE 10.x

deleted-user-74-8Fy1vCPqk — Wed, 06 Mar 2013 17:17:17 GMT

Jack-

Oof.....that was a while ago and if I remember correctly I either didn't do it, or did it the hard way. 😄

Sorry I can't be of better help!

Dan

Re: Updating Field Alias Names Using SQL Management Studio ----> ArcSDE 10.x

RayChilcote — Thu, 07 Mar 2013 11:00:50 GMT

First, vote up the idea of a geoprocessing tool so we can update many fields (not one at a time in ArcCatalog):
http://ideas.arcgis.com/ideaView?id=087300000008IBT&returnUrl=%2Fapex%2FideaList%3Fc%3D09a300000004xET%26category%3DService%2BIdeas%26p%3D2

I purposely moved away from database table updates because of the system table changes. There is also a VB method, but that too accessed the tables directly. Here are the methods I know of:

1. Update using Python and ctypes. This is a heavy work around. Depending on your workflow, might be good.
http://gis.stackexchange.com/questions/255/arcgis-change-feature-class-and-field-aliases-in-bulk

2. Currently, I'm using custom python scripting with FeatureClassToFeatureClass_conversion. Basically creates the FeatureClassToFeatureClass_conversion statement. While creating the statement, does a lookup in a csv file for fieldname and the appropriate field alias name. Ctypes above does this same type method. Hate this idea for numerous reasons (not updated in place, more required scripting to obtain other source database features: Domains, subtypes, attribute indexes, etc.).

3. Some Studio incarnation must be out there. I'm a database guy, so come on Application Developers! Chris Fox may have well done this, as stated in his response in MyIdeas. Here's his toolbox as refered to in the MyIdeas suggestion:
http://www.arcgis.com/home/item.html?id=4c0bcc95449f4a67822c7f68dc7c6cd4
I tried this, but our local machines are so tied down, I can hardly breathe.:rolleyes:

Good luck! Remember, vote up in MyIdeas.

Re: Updating Field Alias Names Using SQL Management Studio ----> ArcSDE 10.x

ThomasColson — Fri, 08 Mar 2013 20:44:10 GMT

The only way to do this is with xquery (xml-sql query), as field alias' live inside sde_items (or something similar). Your xquery would have to find the right xml node and update. Hard, but doable, and worth it only if you have to do millions of FC's. by the time you're done writing the xquery syntax, you would have been long done doing it through arc cat.

Re: Updating Field Alias Names Using SQL Management Studio ----> ArcSDE 10.x

AndrzejBieniek — Sat, 11 Dec 2021 15:52:15 GMT

In case of updating existing Alias only second query can be run.
If the Alias does not exist yet both queries have to be run - please remember to change your ObjectIDs:

/*remove the node in case there is no value yet in the database for the Allias name */
update sde.GDB_ITEMS 
set Definition.modify('delete(/DEFeatureClassInfo/AliasName)')
/*Provide the ObjectId of the feature class - can be checked in properties ArcCatalog 10.1 */
WHERE ObjectId = 1218

/* Provide new alias name in the variable below */
DECLARE @AliasName VARCHAR(1024) = 'Area Committees'
UPDATE sde.GDB_ITEMS
SET Definition.modify('insert <AliasName>{sql:variable("@AliasName")}</AliasName> into(/DEFeatureClassInfo)[1]')
/*Provide the ObjectId of the feature class - can be checked in properties ArcCatalog 10.1 */
WHERE OBJECTID=1218

Re: Updating Field Alias Names Using SQL Management Studio ----> ArcSDE 10.x

RussellBrennan — Wed, 06 Nov 2013 14:38:07 GMT

On a semi-related note, in the upcoming 10.2.1 there will be a GP tool (alter field properties) that will allow you to rename a field as well as change the field alias. So if you can wait until the 10.2.1 release and are willing to use geoprocessing this should help you out.

Re: Updating Field Alias Names Using SQL Management Studio ----> ArcSDE 10.x

MichelleMestrovich1 — Wed, 13 Apr 2016 15:38:13 GMT

I do this in python to update my publication date in the metadata when I truncate and fill a layer from our edit database to our main database. It works pretty well for me.

def UpdateMetadataPublicationDate(LayerToUpdate):

LayerToUpdate = string.split(LayerToUpdate,'\\')[-1]

sde = r'Database Connections\SDE.sde'

sde_conn = arcpy.ArcSDESQLExecute(sde)

sql = '''USE GIS;DECLARE @now varchar(19) = convert(varchar(19),GETDATE(), 126);UPDATE GIS.sde.GDB_ITEMS SET Documentation.modify('replace value of (//metadata/dataIdInfo/idCitation/date/pubDate/text())[1] with sql:variable({0})') where Name = \'{1}\''''.format('\"@now\"',LayerToUpdate)

sde_conn.startTransaction()

sde_conn.execute(sql)

sde_conn.commitTransaction()