Capture SQL View Definition for Programmatic Change

[Patrick]

Hi Experts,

I need to replicate a SAS 9.4 data environment in a Linux environment. Using rsync -av works like a charm for this.

The challenge I'm facing is that the source environment also contains SQL views in the form:

select *
from lref.table
using libname lref "<source_root_path>/<relative path>";

I need to re-create these views in the target environment by replacing the source_root_path with the target_root_path.

Do you know of any other/better way than executing a SQL describe view, capture the log output in a file, parse out the view definition, change the path and then use this new code to create the views in the target environment?

I couldn't figure out any other way to retrieve the view definition.

Thanks, Patrick

[Kurt_Bremser]

UNIX systems have very nice tools for exactly such mass text change operations, e.g. awk. Just run that on the original source codes of the SQL views. If those source codes are not available, someone fucked up big time (see Maxim 27).

[Patrick]

Hi @Kurt_Bremser 

Thanks for answering. 

There are no explicit scripts. The views get created metadata driven. Below an example of the main "table" for this.

What I'm trying to do, is to create the data environment for an integration environment using the data from source that has been defined as "source of truth" and against which data needs to get reconciled (after running for another month).

In theory I wouldn't have to bother about the views, because they should get re-created by such a run. ...but one can't be sure.

I could of course use the config Excels and the existing scripts from source to build some adhoc job for re-creating the views in the target environment, BUT... I really would like to avoid this. Things are done messy. 
What I would like to do, is to create the data (views included) for this integration environment without any dependency on any version of Excel config sheets or scripts.

I hope this explains why I'm trying to extract and use the view definitions to re-create the views. 

Regards, Patrick

[Quentin]

AI had a reasonable suggestion:

In your target environment, could you create a symlink that points  source_root_path  to target_root_path?

That would avoid code changes.