SQL SDE Metadata or Change History

[MyEthiopia]

Hi everyone, we currently have on Enterprise 10.7, and we will be migrating to Enterprise 10.9. Before migrating, I am planning to create an Audit spreadsheet for our small group individuals to go through each dataset. With a set of questions, to make everyone's life easier I wanted to know if there is the way to get a complete history of the feature creation date, name, modified date, attachment, versioning and more. I have admin rights, but I don't have direct access to the SQL server which IT has.

[RyanUthoff · Accepted solution]

The Generate Schema Report tool in ArcGIS Pro might be helpful for you. It might not tell you every piece of information you need to know, but it's an easy way to generate a report of everything in an EGDB.

As far as getting stuff like creation dates and modified date, I'm assuming you want that information at the table level. I'm not sure what type of SQL you're using, but in MSSQL, you can open SSMS and go to the table properties and see the creation date.

EDIT: The last reply in this thread gives you a query you can use to return a list of versioned datasets. https://community.esri.com/t5/geodatabase-questions/how-to-identify-branch-versioned-feature-classes...

[MyEthiopia]

Thank you, Ryan, the Generate Schema Report is very helpful. I am using MSSQL. It would be IT that would have access to SSMS. Didn't want to a submit a request if I can see it on my end.  Do you know if I can use generate schema report for the whole dataset and not just a dataset in the geodatabase

[MyEthiopia]

NVM I just saw the video it looks like navigating to the geoprocessing tool through the export solves my issue of generating a report of the whole dataset. 

[MarceloMarques]

@MyEthiopia you wrote: I wanted to know if there is the way to get a complete history of the feature creation date, name, modified date, attachment, versioning and more.

If you have Geodatabase Editor Tracking Enabled for the featureclass(es) then there are fields that will display information about the creation and updates of each feature.

If you have Geodatabase Archiving Enabled then you have the history of updates and you can even query the data at a particular time in the past.

The Geodatabase Versions you can see a list of all the versions if you right click the geodatabase connection and click administration > versions.

If you have not enabled any of these Geodatabase features on your Geodatabase featureclasses then there is nothing directly in SQL Server that you can use to track the information that you are seeking.

I hope this helps.

[MyEthiopia]

Thank you Marcelo, it looks like we don’t have it set up for most of our data’s. When the sql was first span up it wasn’t in a formal way. We want to standardize most of it now, was hoping to fill out most of the information so people would be interested in filling out the essential information. Do you by chance have any documents on how to best manage database ?

[MarceloMarques]

@MyEthiopia you and your team need to consider attending advanced Geodatabase training, and it is very important to have someone in the team to have advanced SQL Server Administration skills as well.

Esri Geodatabase Training

Deploying and Maintaining a Multiuser Geodatabase | Esri Training Instructor-Led Course

Implementing Versioned Workflows in a Multiuser Geodatabase | Esri Training Instructor-Led Course

Configuring Branch Versioning in ArcGIS | Esri Training Instructor-Led Course

Managing Geospatial Data in ArcGIS | Esri Training Instructor-Led Course

SQL Server Training

Educational SQL resources - SQL Server | Microsoft Learn

Browse all courses, learning paths, and modules - Training | Microsoft Learn

Note: there are many Microsoft training partners that you can consider taking an instructor-led class.

Geodatabase Best Practices

You can find my database template scripts and database guide books for SQL Server in my community.esri.com blog below. You can start with the Production Mapping database guide book, the best practices can be applied to any industry, then move into the database template scripts.

Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

Note: The guide books are intended for database administrators as recommendations for establishing the product workspaces in an Enterprise Geodatabase. For Professional Database Administrators, requires advanced RDBMS and advanced Geodatabase experience.

I hope this helps.

[MyEthiopia]

Thanks @MarceloMarques I will look into the courses. Our new server environment will be done by a contractor, who will obviously train us on the best practices which should be helpful.