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

[deleted-user-74-8Fy1vCPqk]

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

[JackZHANG]

Got same question here. have you sorted it out?

[deleted-user-74-8Fy1vCPqk]

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

[RayChilcote]

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%3D09a300000004x...

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.

[ThomasColson]

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.

[AndrzejBieniek]

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

[RussellBrennan]

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.

[MichelleMestrovich1]

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()