SqlParser.py

The Python project sqlparse will be used, to extract column references from database views SQL code.

A Python script SqlParser.py was created.

  • to read sql_modules_definition

  • to write the parsed view definition back into the repository database

usage

The Python Script SqlParser.py can be executed with parameters or with an ini-file (default is SqlParser.ini in the same directory).
2 parameters are required:

  • server

  • database

When you are in the folder, where the script is located, for example here

D:
cd D:\Repos\gitlab\DataHandwerk\DataHandwerk-toolkit-mssql\python\SqlParser

and the SqlParser.ini contains your server and database, then you can just call the script:

py sqlparser.py

or you can use parameters:

py sqlparser.py --server localhost\sql2019 --database dhw_master

In case Python is not installed on your PC the generated windows executable can be used

D:
cd D:\Repos\gitlab\DataHandwerk\DataHandwerk-toolkit-mssql\python\SqlParser\dist\SqlParser
SqlParser --server localhost\sql2019 --database dhw_self
D:\Repos\gitlab\DataHandwerk\DataHandwerk-toolkit-mssql\python\SqlParser\dist\SqlParser>SqlParser.exe
usage: SqlParser.exe [-h] --server SERVER --database DATABASE [-c MY_CONFIG] [-v]
SqlParser.exe: error: the following arguments are required: --server, --database

what does it do

The following query is used to read the [sql_modules_definition] for views, but only in case it was changed since last parsing

SELECT
    [RepoObject_guid]
    , [sql_modules_definition]
FROM
    [sqlparse].[RepoObject_SqlModules_Repo_Sys]
WHERE
    is_outdated = 1
    AND sysobject_type = 'V'

the sqlparse results are written into [sqlparse].[RepoObject_SqlModules]

INSERT INTO [sqlparse].[RepoObject_SqlModules]
([RepoObject_guid]
,[sql_modules_formatted]
,[sql_modules_formatted2]
,[sql_modules_json])
VALUES
(?, ?, ?, ?)

How to freeze the script (create exe executable)

Instruction for the developer, when the script changes.

call this from the folder, where SqlParser.py is located:

pyinstaller SqlParser.py

this will create one-folder-executable in .\dist\SqlParser

It is also possible to create a onefile-executable, but the "Bundling to One Folder" has some advantages (read the usage documentation for pyinstaller)

pyinstaller --onefile --windowed SqlParser.py