Modified date of feature class in SDE
Ken,
I'm using the following query to get the last edit date for versioned feature classes:
SQL CODE
SELECT
r.OWNER,
r.TABLE_NAME,
TO_DATE ('19700101000000','YYYYMMDDHH24MISS') + NUMTODSINTERVAL(r.REGISTRATION_DATE,'SECOND') REGISTRATION_DATE,
m.STATE_ID,
s.CLOSING_TIME EDIT_DATE
FROM
<OWNER>.TABLE_REGISTRY r,
(
SELECT
MAX(m.STATE_ID) STATE_ID,
m.REGISTRATION_ID
FROM
<OWNER>.MVTABLES_MODIFIED m
GROUP BY
m.REGISTRATION_ID
) m,
<OWNER>.STATES s
WHERE
r.TABLE_NAME NOT LIKE '%DIRTYAREAS' AND
r.TABLE_NAME NOT LIKE '%LINEERRORS' AND
r.TABLE_NAME NOT LIKE '%POINTERRORS' AND
r.TABLE_NAME NOT LIKE '%POLYERRORS' AND
r.TABLE_NAME NOT LIKE 'GDB_%' AND
r.REGISTRATION_ID ( + ) = m.REGISTRATION_ID AND
s.STATE_ID = m.STATE_ID
ORDER BY
m.STATE_ID DESC
Substitute <OWNER> for the name of your geodatabase owner in the SQL above.
I wrote the following geoprocessing script tool In Python for the user to use with ArcToolbox:
GUI
GUI VALIDATOR CODE
# -*- coding: utf-8 -*-
# ---------------------------------------------------------------------------
# GetVersionedEditDates_Validator.py
# Created on: 2015-06-03
# John P. Lovato
# Description:
# Validator for GetVersionedEditDates script tool.
# Updated on:
# 2015-06-03 by John P. Lovato - Created.
#
# ---------------------------------------------------------------------------
import arcpy
class ToolValidator(object):
"""Class for validating a tool's parameter values and controlling
the behavior of the tool's dialog."""
def __init__(self):
"""Setup arcpy and the list of tool parameters."""
self.params = arcpy.GetParameterInfo()
def initializeParameters(self):
"""Refine the properties of a tool's parameters. This method is
called when the tool is opened."""
# disable the CSV File location parameter on start up
self.params[3].enabled = 0
return
def updateParameters(self):
"""Modify the values and properties of parameters before internal
validation is performed. This method is called whenever a parameter
has been changed."""
# toggle the state of the CSV file location parameter baed on
# checkbox.
if self.params[2].value == True:
self.params[3].enabled = 1
else:
self.params[3].enabled = 0
return
def updateMessages(self):
"""Modify the messages created by internal validation for each tool
parameter. This method is called after internal validation."""
return
STANDALONE PYTHON SCRIPT
# -*- coding: utf-8 -*-
# ---------------------------------------------------------------------------
# GetVersionedEditDates.py
# Created on: 2015-06-02
# John P. Lovato
# Description:
# Displays registration date and last edit date for versioned objects
# Arguments:
# <SDE_Workspace> - Workspace connection.
# <GDB_Owner> - Geodatabase Owner (User who owns GDB metadata tables)
# <Write_CSV> - Should the results go to a CSV file.
# Updated on:
# 2015-06-03 by John P. Lovato - Added parameters for writing csv file and code
# for creating CSV file.
# ---------------------------------------------------------------------------
import arcpy, sys, os, datetime, csv
from arcpy import env
from datetime import datetime
# SDE Workspace
sdeWorkspace = arcpy.GetParameter(0)
# GDB Owner
gdbOwner = arcpy.GetParameter(1)
# Write CSV
writeCSV = arcpy.GetParameter(2)
# CSV File
outputFolder = arcpy.GetParameterAsText(3)
# Establish the gp workspace
env.workspace = sys.path[0]
# Create the SDE Connection
sdeConn = arcpy.ArcSDESQLExecute(sdeWorkspace)
# Build the SQL Statement for last versioned edits (DML changes only)
SQLStatement= """SELECT
r.OWNER,
r.TABLE_NAME,
TO_DATE ('19700101000000','YYYYMMDDHH24MISS') + NUMTODSINTERVAL(r.REGISTRATION_DATE,'SECOND') REGISTRATION_DATE,
m.STATE_ID,
s.CLOSING_TIME EDIT_DATE
FROM
<OWNER>.TABLE_REGISTRY r,
(
SELECT
MAX(m.STATE_ID) STATE_ID,
m.REGISTRATION_ID
FROM
<OWNER>.MVTABLES_MODIFIED m
GROUP BY
m.REGISTRATION_ID
) m,
<OWNER>.STATES s
WHERE
r.TABLE_NAME NOT LIKE '%DIRTYAREAS' AND
r.TABLE_NAME NOT LIKE '%LINEERRORS' AND
r.TABLE_NAME NOT LIKE '%POINTERRORS' AND
r.TABLE_NAME NOT LIKE '%POLYERRORS' AND
r.TABLE_NAME NOT LIKE 'GDB_%' AND
r.REGISTRATION_ID ( + ) = m.REGISTRATION_ID AND
s.STATE_ID = m.STATE_ID
ORDER BY
m.STATE_ID DESC"""
# Customize the SQL Statement
SQLStatement = SQLStatement.replace('<OWNER>',gdbOwner)
# Display the SQL Statement
arcpy.AddMessage("\n" + SQLStatement + "\n")
# Pass the SQL statement to the database.
sdeReturn = sdeConn.execute(SQLStatement)
if writeCSV:
# Directory of current script
dirScript = os.path.dirname(__file__)
# Output Folder
if outputFolder:
dirScript = outputFolder
# Report Date
reportDate = str(datetime.now()).replace(' ','_').replace(':','-').replace('.','-')
# Output File
outputFile = os.path.join(dirScript,gdbOwner + '_DML_Modified_' + reportDate + '.csv')
arcpy.AddMessage('Report will be written to ' + outputFile + '\n')
# Display the header to output
arcpy.AddMessage('{:<30}'.format('OWNER')+' '+'{:<30}'.format('TABLE')+' '+'{:^21}'.format('REG_DATE')+' '+'{:^8}'.format('STATE_ID')+' '+'{:^21}'.format('EDIT_DATE'))
arcpy.AddMessage('{:<30}'.format('------------------------------')+' '+'{:<30}'.format('------------------------------')+' '+'{:^21}'.format('---------------------')+' '+'{:^8}'.format('--------')+' '+'{:^21}'.format('---------------------'))
# Display (and capture) the results
with open(outputFile, 'wb') as csvfile:
csvwriter = csv.writer(csvfile)
# write the header
csvwriter.writerow(['OWNER','TABLE','REG_DATE','STATE_ID','EDIT_DATE'])
for sde in sdeReturn:
arcpy.AddMessage('{:<30}'.format(sde[0])+' '+'{:<30}'.format(sde[1])+' '+'{:<21}'.format(sde[2])+' '+'{:^8}'.format(sde[3])+' '+'{:<21}'.format(sde[4]))
csvwriter.writerow([str(sde[0]),str(sde[1]),str(sde[2]),str(sde[3]),str(sde[4])])
arcpy.AddMessage("\n+++++++++\n")
else:
# Display the header to output
arcpy.AddMessage('{:<30}'.format('OWNER')+' '+'{:<30}'.format('TABLE')+' '+'{:^21}'.format('REG_DATE')+' '+'{:^8}'.format('STATE_ID')+' '+'{:^21}'.format('EDIT_DATE'))
arcpy.AddMessage('{:<30}'.format('------------------------------')+' '+'{:<30}'.format('------------------------------')+' '+'{:^21}'.format('---------------------')+' '+'{:^8}'.format('--------')+' '+'{:^21}'.format('---------------------'))
# Display the results only
for sde in sdeReturn:
arcpy.AddMessage('{:<30}'.format(sde[0])+' '+'{:<30}'.format(sde[1])+' '+'{:<21}'.format(sde[2])+' '+'{:^8}'.format(sde[3])+' '+'{:<21}'.format(sde[4]))
arcpy.AddMessage("\n+++++++++\n")
Hope this helps you.
Sincerely,
John P. Lovato
USDA Forest Service
Geospatial Database Engineer
