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:

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