topic avHow to retrive the modification date of an ArcSDE elements? in Python Questions

avHow to retrive the modification date of an ArcSDE elements?

SaidAkif — Fri, 07 Aug 2015 13:11:26 GMT

Hi all,

I work with an arcSDE database (SQL server Enterprise geodatabase) and I want to retrive using python code the modification date of each of its elements (tables, features classes, rasters, ...)

Thanks

Re: avHow to retrive the modification date of an ArcSDE elements?

ChristianWells — Fri, 07 Aug 2015 14:36:42 GMT

Hi Said,

When you say modified date, do you mean table structure or last updated/added row?

Re: avHow to retrive the modification date of an ArcSDE elements?

SaidAkif — Fri, 07 Aug 2015 14:44:27 GMT

I Mean last feauture class modification or the date of creation

Thanks for your quick answer

Re: avHow to retrive the modification date of an ArcSDE elements?

ChristianWells — Sun, 12 Dec 2021 07:29:28 GMT

Hi Said,

This is something that can be done by sending SQL queries to your SDE repository tables to get the created UNIX time from the sde_table_registry and convert that to a more standard time. I have attached a script I have used to perform this in the past and write it out to a logfile.

However, this should be enough to get you started on how to access and convert the registration time in your Enterprise Geodatabase.

import arcpy, os, datetime, time
workPath = r"Database Connections\Connection to cwells.sde"
arcpy.CreateDatabaseView_management(workPath, viewName, "select database_name, owner, table_name, registration_date from sde.sde_table_registry")
for row in arcpy.SearchCursor(os.path.join(workPath, viewName)):
    tableName = row.getValue("DATABASE_NAME") + "." + row.getValue("OWNER") + "." + row.getValue("TABLE_NAME")
    cDate = row.getValue("REGISTRATION_DATE")
    dTime = time.strftime('%d-%b-%Y %H:%M:%S', time.localtime(cDate))

Re: avHow to retrive the modification date of an ArcSDE elements?

SaidAkif — Fri, 07 Aug 2015 15:33:13 GMT

Thanks

Is there a possibility to have just one date or the date creation or a last modification of a feature class instead of looping in all rows

Re: avHow to retrive the modification date of an ArcSDE elements?

ChristianWells — Fri, 07 Aug 2015 15:35:43 GMT

I'm not sure of your exact question, but are you asking if its possible to return a date for a single feature class only?

Re: avHow to retrive the modification date of an ArcSDE elements?

SaidAkif — Fri, 07 Aug 2015 17:34:42 GMT

Some time you want to know the date when a feature classe was added to a dataset or when was its last modification. it is about those kind of information

Re: avHow to retrive the modification date of an ArcSDE elements?

ChristianWells — Sun, 12 Dec 2021 07:29:31 GMT

Here is a sample of a single feature class:

import arcpy, os, datetime, time
gdb = r"Database Connections\Connection to cwells.sde"
conn = arcpy.ArcSDESQLExecute(gdb)

owner = 'sde'
table = 'alex'

sql = """SELECT
create_date,modify_date
FROM sys.tables
where UPPER(SCHEMA_NAME(schema_id)) = '{0}' AND
  UPPER(OBJECT_NAME(object_id)) = '{1}'"""

dates = conn.execute(sql.format(owner.upper(), table.upper()))

print (owner + '.' + table).upper()
print "Create Date: {0}".format(dates[0][0])
print "Modify Date: {0}".format(dates[0][1])

OUTPUT

SDE.ALEX
Create Date: 8/3/2015 10:55:11 AM
Modify Date: 8/3/2015 11:00:51 AM

Re: avHow to retrive the modification date of an ArcSDE elements?

SaidAkif — Fri, 07 Aug 2015 19:44:21 GMT

Sorry but how can I try it with a single feature class

Thanks

Re: avHow to retrive the modification date of an ArcSDE elements?

ChristianWells — Fri, 07 Aug 2015 21:16:17 GMT

You can change the "owner" and "table" variables to be your feature class, table or raster.

So if the feature class you want to get information for appears as "gis.abc.polygons" the variable would be:

owner = 'abc'

table = 'polygons'

Re: avHow to retrive the modification date of an ArcSDE elements?

SaidAkif — Tue, 08 Sep 2015 19:21:50 GMT

Thanks. This is really helpful

Re: avHow to retrive the modification date of an ArcSDE elements?

SaidAkif — Thu, 01 Oct 2015 14:12:05 GMT

Hi Christian
I tried your code before and it worked well. Not I got kind of error. Here is what I did:
gdb = r'Database Connections\GKMI.sde'
owner = 'sde' I let it as sde
table='GKMIGeodatabase.ATLR.CH_ATL_PipingPlover" hre is the name of my table or feature class

I run the script. Unfortunatly, the dates was just boolean and = True

I don't know what I did wrong?
Could you please give me more explanation on the owner and table variable significations?

Thankd

Re: avHow to retrive the modification date of an ArcSDE elements?

SaidAkif — Thu, 01 Oct 2015 17:22:04 GMT

I tried this example:

sql =

"SELECT * FROM sys.tables"

sde_return = sde_conn.execute(sql)

after I retreive the first value of sde_return[

0] =

[u'SDE_GEOMETRY3320', 223997, None, 45, 0, u'U ', u'USER_TABLE', u'20/03/2014 10:32:12 AM', u'27/09/2015 8:55:24 AM', 0, 0, 0, 1, None, 2, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, u'TABLE', 0]

Do you know please how can i get the signification of each value (field names of sys.tables)

Thanks