Yes, there is a way to download all workbooks to a given machine. However, that is not necessarily the fastest or easiest way to solve your problem. We recently released a new feature called Tableau Catalog in 2019.3 that should make getting the information you need very easy. If the UI features are not sufficient, you also get direct access to the underlying data via the Metadata API.
Another way to go is to use a data source I built against the Tableau Server PostgreSQL database called TS Data Connections, which would let you analyze all connections used by workbooks on Tableau Server. It will know the data server, but not the actual database, so it will be useful if you are okay with a broader search.
Finally, your desire to download all workbooks can be done both in a supported way, with the REST API call to Get Workbooks and then Download Workbook, or in an unsupported way, by querying the PostgreSQL repository data and extracting the .twb files directly from the blob format they're stored in. The latter technique tends to be much faster and require far less space because it is only pulling XML data, and not the full .twbx data with the extracts along with it. Either of these methods would then require you to parse the XML from each workbook to interrogate what database it used.
On further review… I’m not sure that any of these methods do exactly what I need.
Here’s the crux of the issue. Many of our workbooks have “custom sql” data sources. I need an automated way to extract all of the exact SQL in a way which can then be tied back to the workbook it came from, so I can have my DBA optimize the SQL as a view, then change the binding of the workbook to the view.
Can you suggest an efficient way of doing this?