How to run a SQL report

Running a SQL report

Steps to run a SQL report

Step 1: Open pgAdmin
Step 2: Click on Execute arbitrary SQL queries, connected to your PosgreSQL instance:
Step 3: Copy your script in SQL Editor
Step 4: Click Replace All, to make the replacements.
Step 5: Change the output of your script : menu Query, option Execute to file, change the file name, than click OK. You will receive the confirmation of your export: Data export completed successfully.
Step 6: Open your file and check the result

SQL Reports

List of the columns

The attached SQL file list_of_the_columns.sql could be used to generate the list of the columns, here is the description:

Database name, if one
Schema name
Table name
Column name
Column type

List of the transactions and columns / tables accessed by transactions

The attached SQL file list_of_transactions_columns.sql could be used to generate the list of the columns / tables accessed by transactions, here is the description:

Transaction Name
Technical name of the transaction, full name
Database name, if one
Schema name
Table name
Column name
Kind of the access: READ / WRITE

Analysis Unit Reports

Hierarchy of the objects

There are two unit reports:

For the first one the input is a folder, e.g. deployment folder,
For the second the input is a knowledge base. The format of the output is JSON.

Command line when the input is a folder:
fullnames_report.read_path_symbols(‘folder_name’)
e.g.:

fullnames_report.read_path_symbols('../../tests/diags')

Examples:

Generated_6f62ed57-ceee-4610-a703-ccb2c9802fdc_PreReport.json - Generated_3c59dd03-c6ff-4842-8602-dca85dd298f2_PreReport.json

Command line when the input is a knowledge base:

server = Server()kb = server.get_schema('local_name')application =
kb.get_application('application_name')kb_fullnames_report.KBFullNamesReport.start_application(self,application)e.g.:server
= Server()kb = server.get_schema('sqlreports_local')application =
kb.get_application('castpubs')kb_fullnames_report.KBFullNamesReport.start_application(self,application)

Examples:

List of the objects having the same name in different schemas

Command line:
fullnames_report.read_path_symbols(‘folder_name’,2)
e.g.:
fullnames_report.read_path_symbols(‘duplicated_objects’,2)

The output is text format and it looks like:

ObjectFullName;File
TOTO.utilisateur;duplicated_objects\test1.sql
TITI.utilisateur;duplicated_objects\test1.sql

List of the columns with sensitivity for data sensitivity

The report expected to have a parametrization file, e.g.: sensitivity_columns_param.txt

Command line:
columns_report.read_columns_from_path(‘sql_folder_name’, ‘.parametrization_file_name’)
e.g.:
columns_report.read_columns_from_path(’../../tests/diags’, ‘..\sensitivity_columns_param.txt’)
columns_report.read_columns_from_path(’../../tests/mssql/castpubs’, ‘..\sensitivity_columns_param.txt’)

Example report: Generated_Sensitivity_c1d8c183-539f-4e25-8a36-0ea818d5632c.datasensitive