Seeking a DB query to list specific workflows

[Durell Demartini]

Hello,

We use Jira Misc Workflow Extensions and have added several Field Required Validators to several workflows to make a specific field required. We were wondering if there's a DB query someone is aware of that could list all workflows where a specific custom field is made required. Ideally it would be great to know the transitions where the validator exists - not sure if this is even possible. 

Thanks for your time. 

[John Price · Accepted solution]

Since you're already mucking around in the database (definitely get into the REST APIs as it's better practice and the SQL won't work if you go to Cloud someday), have a look here:

https://confluence.atlassian.com/jirakb/get-list-all-the-statuses-used-in-all-the-active-workflows-from-database-1189791339.html

The full definition of a workflow is stored in a single column containing XML. Depending on the database backend, you will have different SQL functions to query XML in a column, and you may have to do some data type casts or conversions. I have done this in the past and once you know the workflow XML format, it should be relatively easy to grab.  Make sure to save all your SQL snippets in Confluence or someplace!

Also, I have started to use Automation rules over workflow functions/triggers/etc. in a lot of cases, because they are easier to see and fiddle with in the UI. That's not relevant here but something to think about.