Task: equi-join-detect (source dataset: Auto-BI)

Your are given a database with related tables used for business intelligence analysis. Your task is to identify all key/foreign-key join relationships between the tables for this database. Recall that a join is a relationship between two tables, that allows rows from one table to be combined with rows from another, based on a set of common join columns.
 
 
No explanation needed. Please return all join relationships in the following JSON format:
{
  "joins": [
    {
      "from_table": "table-1",
      "from_column": "column-1",
      "to_table": "table-2",
      "to_column": "column-3"
    }, 
	...
  ]
}

Tables:

Table: sysjobactivity
|   session_id | job_id                               | run_requested_date   | run_requested_source   | queued_date   | start_execution_date   | last_executed_step_id   | last_executed_step_date   | stop_execution_date   | job_history_id   | next_scheduled_run_date   |
|-------------:|:-------------------------------------|:---------------------|:-----------------------|:--------------|:-----------------------|:------------------------|:--------------------------|:----------------------|:-----------------|:--------------------------|
|            2 | D78D06A4-CAD5-4191-BE1A-E14A639FF299 |                      |                        |               |                        |                         |                           |                       |                  |                           |
|            3 | 6A57C77C-558E-44B0-AB17-9A5ACB33C7AF |                      |                        |               |                        |                         |                           |                       |                  |                           |
|            5 | 8E05469B-CD8B-47CE-9F84-1F83215BE9C1 |                      |                        |               |                        |                         |                           |                       |                  |                           |
|            5 | 2BDDFB10-668F-44F2-B2AD-8B2845151DC6 |                      |                        |               |                        |                         |                           |                       |                  |                           |
|            5 | 967696B7-B0FE-48AD-A216-8EE2CB7AA6FB |                      |                        |               |                        |                         |                           |                       |                  |                           |
|            5 | 150ABE77-D7CE-421A-8B68-972489C12846 |                      |                        |               |                        |                         |                           |                       |                  |                           |
|            5 | 6A57C77C-558E-44B0-AB17-9A5ACB33C7AF |                      |                        |               |                        |                         |                           |                       |                  |                           |
|            5 | B0A5AA37-9EA5-41ED-9143-B8122FDD5D88 |                      |                        |               |                        |                         |                           |                       |                  |                           |
|            5 | B61D15A8-3FD8-4A69-826D-C46E4F4B10CB |                      |                        |               |                        |                         |                           |                       |                  |                           |
|            5 | 47101CF6-B170-440C-9397-D8BDA4D856F5 |                      |                        |               |                        |                         |                           |                       |                  |                           |


Table: sysjobhistory
|   instance_id | job_id                               |   step_id | step_name                          |   sql_message_id |   sql_severity | message                                                          |   run_status |   run_date |   run_time |   run_duration |   operator_id_emailed |   operator_id_netsent |   operator_id_paged |   retries_attempted | server          |
|--------------:|:-------------------------------------|----------:|:-----------------------------------|-----------------:|---------------:|:-----------------------------------------------------------------|-------------:|-----------:|-----------:|---------------:|----------------------:|----------------------:|--------------------:|--------------------:|:----------------|
|          4101 | 402DCE00-0B43-4B0F-A2EE-BCEC022CC0F7 |         1 | Verify that automation is enabled. |                0 |              0 | Executed as user: NT SERVICE\SQLSERVERAGENT. The step succeeded. |            1 |   20201114 |      34832 |              0 |                     0 |                     0 |                   0 |                   0 | DESKTOP-CU621A8 |
|          4102 | 402DCE00-0B43-4B0F-A2EE-BCEC022CC0F7 |         2 | Purge history.                     |                0 |              0 | Executed as user: NT SERVICE\SQLSERVERAGENT. The step succeeded. |            1 |   20201114 |      34832 |              0 |                     0 |                     0 |                   0 |                   0 | DESKTOP-CU621A8 |
|          4108 | 402DCE00-0B43-4B0F-A2EE-BCEC022CC0F7 |         1 | Verify that automation is enabled. |                0 |              0 | Executed as user: NT SERVICE\SQLSERVERAGENT. The step succeeded. |            1 |   20201115 |      51545 |              0 |                     0 |                     0 |                   0 |                   0 | DESKTOP-CU621A8 |
|          4109 | 402DCE00-0B43-4B0F-A2EE-BCEC022CC0F7 |         2 | Purge history.                     |                0 |              0 | Executed as user: NT SERVICE\SQLSERVERAGENT. The step succeeded. |            1 |   20201115 |      51545 |              0 |                     0 |                     0 |                   0 |                   0 | DESKTOP-CU621A8 |
|          4113 | 402DCE00-0B43-4B0F-A2EE-BCEC022CC0F7 |         1 | Verify that automation is enabled. |                0 |              0 | Executed as user: NT SERVICE\SQLSERVERAGENT. The step succeeded. |            1 |   20201116 |      51254 |              0 |                     0 |                     0 |                   0 |                   0 | DESKTOP-CU621A8 |
|          4115 | 402DCE00-0B43-4B0F-A2EE-BCEC022CC0F7 |         2 | Purge history.                     |                0 |              0 | Executed as user: NT SERVICE\SQLSERVERAGENT. The step succeeded. |            1 |   20201116 |      51254 |              0 |                     0 |                     0 |                   0 |                   0 | DESKTOP-CU621A8 |
|          4121 | 402DCE00-0B43-4B0F-A2EE-BCEC022CC0F7 |         2 | Purge history.                     |                0 |              0 | Executed as user: NT SERVICE\SQLSERVERAGENT. The step succeeded. |            1 |   20201117 |      51056 |              0 |                     0 |                     0 |                   0 |                   0 | DESKTOP-CU621A8 |
|          4126 | 402DCE00-0B43-4B0F-A2EE-BCEC022CC0F7 |         1 | Verify that automation is enabled. |                0 |              0 | Executed as user: NT SERVICE\SQLSERVERAGENT. The step succeeded. |            1 |   20201206 |      82926 |              0 |                     0 |                     0 |                   0 |                   0 | DESKTOP-CU621A8 |
|          4128 | 402DCE00-0B43-4B0F-A2EE-BCEC022CC0F7 |         2 | Purge history.                     |                0 |              0 | Executed as user: NT SERVICE\SQLSERVERAGENT. The step succeeded. |            1 |   20201206 |      82926 |              0 |                     0 |                     0 |                   0 |                   0 | DESKTOP-CU621A8 |
|          4228 | 402DCE00-0B43-4B0F-A2EE-BCEC022CC0F7 |         1 | Verify that automation is enabled. |                0 |              0 | Executed as user: NT SERVICE\SQLSERVERAGENT. The step succeeded. |            1 |   20201207 |      20005 |              0 |                     0 |                     0 |                   0 |                   0 | DESKTOP-CU621A8 |


Table: sysjobs
| job_id                               |   originating_server_id | name                                    |   enabled | description                                                                                                                                                       |   start_step_id |   category_id |   notify_level_eventlog |   notify_level_email |   notify_level_netsend |   notify_level_page |   notify_email_operator_id |   notify_netsend_operator_id |   notify_page_operator_id |   delete_level | date_created            | date_modified           |   version_number |
|:-------------------------------------|------------------------:|:----------------------------------------|----------:|:------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------------:|--------------:|------------------------:|---------------------:|-----------------------:|--------------------:|---------------------------:|-----------------------------:|--------------------------:|---------------:|:------------------------|:------------------------|-----------------:|
| D1EEB7AF-EB95-4519-8C66-1A232FA30E7E |                       0 | LOAD SQL AGENT JOB HISTORY              |         1 | No description available.                                                                                                                                         |               1 |             0 |                       0 |                    0 |                      0 |                   0 |                          0 |                            0 |                         0 |              0 | 2020-11-01 09:15:32.816 | 2020-11-01 09:15:33.230 |                3 |
| 8E05469B-CD8B-47CE-9F84-1F83215BE9C1 |                       0 | DEMO 15-25 MINUTES                      |         1 | No description available.                                                                                                                                         |               1 |             0 |                       0 |                    0 |                      0 |                   0 |                          0 |                            0 |                         0 |              0 | 2020-10-15 06:19:15.280 | 2020-10-15 06:19:15.333 |                3 |
| 53B2CDC2-74C2-4A13-A1A4-2ED0A8B9440A |                       0 | SQL AGENT JOB STATE DEMO                |         1 | No description available.                                                                                                                                         |               1 |             0 |                       0 |                    0 |                      0 |                   0 |                          0 |                            0 |                         0 |              0 | 2020-11-13 13:49:39.246 | 2020-11-13 13:53:04.463 |               14 |
| F22988D2-B5A2-44F6-AAE1-31C1DA6CE8C1 |                       0 | CAPTURE SQL AGENT JOBS RUNNING SNAPSHOT |         1 | No description available.                                                                                                                                         |               1 |             0 |                       0 |                    0 |                      0 |                   0 |                          0 |                            0 |                         0 |              0 | 2021-03-20 15:55:45.876 | 2021-03-20 15:55:45.933 |                3 |
| F1D358E6-FF5D-4494-AFF3-5B71465BB05A |                       0 | PowerShell Test                         |         1 | No description available.                                                                                                                                         |               1 |             0 |                       0 |                    0 |                      0 |                   0 |                          0 |                            0 |                         0 |              0 | 2020-12-06 09:12:59.816 | 2020-12-06 17:19:22.270 |               35 |
| A93A5FB9-CA6B-4F3F-BF3B-624F0ECC649C |                       0 | BSSUG - CALCULATE JOB HISTORY           |         1 | No description available.                                                                                                                                         |               1 |             0 |                       0 |                    0 |                      0 |                   0 |                          0 |                            0 |                         0 |              0 | 2020-10-17 08:11:21.083 | 2020-10-17 08:19:30.590 |                9 |
| 31C98C59-FEAB-4D08-934E-64AE142EA2C8 |                       0 | ONE STEP                                |         1 | No description available.                                                                                                                                         |               1 |             0 |                       0 |                    0 |                      0 |                   0 |                          0 |                            0 |                         0 |              0 | 2020-10-28 05:59:55.106 | 2020-10-28 06:02:45.546 |                4 |
| 7B9C1D34-FD22-40D4-9BCA-6D62EB42D8C8 |                       0 | BSSUG - SQL AGENT DATA MART ETL         |         0 | No description available.                                                                                                                                         |               1 |             0 |                       0 |                    0 |                      0 |                   0 |                          0 |                            0 |                         0 |              0 | 2020-10-17 08:13:28.620 | 2020-10-17 17:19:13.060 |                5 |
| 9A6F66E9-B510-4FBE-854D-6F1AEDF6EB18 |                       0 | SSIS Server Maintenance Job             |         1 | Runs every day. The job removes operation records from the database that are outside the retention window and maintains a maximum number of versions per project. |               1 |             0 |                       2 |                    0 |                      0 |                   0 |                          0 |                            0 |                         0 |              0 | 2020-10-10 13:25:17.163 | 2021-03-04 20:10:02.683 |                7 |
| 4E2D01C1-FAF9-4CD4-8183-7E8CE025AB8F |                       0 | REFRESH HR DASHBOARD                    |         1 | No description available.                                                                                                                                         |               1 |             0 |                       0 |                    0 |                      0 |                   0 |                          0 |                            0 |                         0 |              0 | 2020-10-25 09:26:07.083 | 2020-10-25 09:26:07.373 |                3 |


Table: sysjobsteps
| job_id                               |   step_id | step_name                   | subsystem   | command                                                                                                                                                                                                                                                                                |   flags | additional_parameters   |   cmdexec_success_code |   on_success_action |   on_success_step_id |   on_fail_action |   on_fail_step_id | server   | database_name       | database_user_name   |   retry_attempts |   retry_interval |   os_run_priority | output_file_name   |   last_run_outcome |   last_run_duration |   last_run_retries |   last_run_date |   last_run_time | proxy_id   | step_uid                             |
|:-------------------------------------|----------:|:----------------------------|:------------|:---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------:|:------------------------|-----------------------:|--------------------:|---------------------:|-----------------:|------------------:|:---------|:--------------------|:---------------------|-----------------:|-----------------:|------------------:|:-------------------|-------------------:|--------------------:|-------------------:|----------------:|----------------:|:-----------|:-------------------------------------|
| D1EEB7AF-EB95-4519-8C66-1A232FA30E7E |         1 | PURGE SQL AGENT JOB HISTORY | SSIS        | /ISSERVER "\"\SSISDB\SQL-AGENT-DEMO\SQL_AGENT_JOB_STATE_DEMO\PURGE_SQL_AGENT_JOB_HISTORY.dtsx\"" /SERVER localhost /ENVREFERENCE 2 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E             |       0 |                         |                      0 |                   1 |                    0 |                2 |                 0 |          | master              |                      |                0 |                0 |                 0 |                    |                  0 |                   0 |                  0 |               0 |               0 |            | 7F7F544B-FE25-42C4-9001-FA23296F4706 |
| 8E05469B-CD8B-47CE-9F84-1F83215BE9C1 |         1 | STEP 1                      | TSQL        | EXEC [dbo].[SimulateJobStepDuration]                                                                                                                                                                                                                                                   |       0 |                         |                      0 |                   1 |                    0 |                2 |                 0 |          | SQL_AGENT_DATA_MART |                      |                0 |                0 |                 0 |                    |                  1 |                1550 |                  0 |        20201020 |           81422 |            | 0C509D30-141B-4CD7-9676-B5A860DBE4E7 |
|                                      |           |                             |             | 	@MINIMUM_MINUTES = 15                                                                                                                                                                                                                                                                 |         |                         |                        |                     |                      |                  |                   |          |                     |                      |                  |                  |                   |                    |                    |                     |                    |                 |                 |            |                                      |
|                                      |           |                             |             | ,	@MAXIMUM_MINUTES = 25;                                                                                                                                                                                                                                                                                      |         |                         |                        |                     |                      |                  |                   |          |                     |                      |                  |                  |                   |                    |                    |                     |                    |                 |                 |            |                                      |
| 53B2CDC2-74C2-4A13-A1A4-2ED0A8B9440A |         1 | Initialize Job State        | SSIS        | /ISSERVER "\"\SSISDB\SQL-AGENT-DEMO\SQL_AGENT_JOB_STATE_DEMO\INITIALIZE_JOB_STATE.dtsx\"" /SERVER localhost /ENVREFERENCE 3 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E                    |       0 |                         |                      0 |                   3 |                    0 |                2 |                 0 |          | master              |                      |                0 |                0 |                 0 |                    |                  1 |                   6 |                  0 |        20201113 |          140628 |            | C8E91238-94B8-4BBB-B8EB-78614D2AEAFA |
| 53B2CDC2-74C2-4A13-A1A4-2ED0A8B9440A |         2 | Stage Job History           | SSIS        | /ISSERVER "\"\SSISDB\SQL-AGENT-DEMO\SQL_AGENT_JOB_STATE_DEMO\STAGE_INCREMENTAL_SQL_AGENT_JOB_HISTORY.dtsx\"" /SERVER localhost /ENVREFERENCE 3 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E |       0 |                         |                      0 |                   3 |                    0 |                2 |                 0 |          | master              |                      |                0 |                0 |                 0 |                    |                  1 |                   3 |                  0 |        20201113 |          140634 |            | 82B538CE-1AC0-4137-853A-10F0B2A3480F |
| 53B2CDC2-74C2-4A13-A1A4-2ED0A8B9440A |         3 | Load Job History            | SSIS        | /ISSERVER "\"\SSISDB\SQL-AGENT-DEMO\SQL_AGENT_JOB_STATE_DEMO\LOAD_INCREMENTAL_SQL_AGENT_JOB_HISTORY.dtsx\"" /SERVER localhost /ENVREFERENCE 3 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E  |       0 |                         |                      0 |                   3 |                    0 |                2 |                 0 |          | master              |                      |                0 |                0 |                 0 |                    |                  1 |                   4 |                  0 |        20201113 |          140637 |            | 3F8B50C5-1E5B-4F35-A3DC-8376A55A8F9B |
| 53B2CDC2-74C2-4A13-A1A4-2ED0A8B9440A |         4 | Purge Job History           | SSIS        | /ISSERVER "\"\SSISDB\SQL-AGENT-DEMO\SQL_AGENT_JOB_STATE_DEMO\PURGE_SQL_AGENT_JOB_HISTORY.dtsx\"" /SERVER localhost /ENVREFERENCE 3 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E             |       0 |                         |                      0 |                   1 |                    0 |                2 |                 0 |          | master              |                      |                0 |                0 |                 0 |                    |                  1 |                   6 |                  0 |        20201113 |          140641 |            | 3FDB6C4B-B7F7-4620-B043-C83A01815BE4 |
| F22988D2-B5A2-44F6-AAE1-31C1DA6CE8C1 |         1 | CAPTURE                     | TSQL        | EXEC dbo.CAPTURE_SQL_AGENT_JOBS_RUNNING_SNAPSHOT;                                                                                                                                                                                                                                      |       0 |                         |                      0 |                   1 |                    0 |                2 |                 0 |          | SQL_AGENT_SNAPSHOT  |                      |                0 |                0 |                 0 |                    |                  1 |                   0 |                  0 |        20210321 |           85620 |            | 2E7E85C1-468B-4E97-8034-FA2F1B231007 |
| F1D358E6-FF5D-4494-AFF3-5B71465BB05A |         1 | Get-ExecutionPolicy         | PowerShell  | Get-ExecutionPolicy -List | Out-File C:\TEMP\ExecutionPolicy.txt                                                                                                                                                                                                                       |       0 |                         |                      0 |                   3 |                    0 |                2 |                 0 |          | master              |                      |                0 |                0 |                 0 |                    |                  1 |                   0 |                  0 |        20201206 |          171928 | 1          | C2FA53D9-C2B6-4DE1-A0ED-AF695028AF7F |
| F1D358E6-FF5D-4494-AFF3-5B71465BB05A |         2 | Profile                     | PowerShell  | $profile | Out-File C:\TEMP\profile.txt                                                                                                                                                                                                                                                |      40 |                         |                      0 |                   3 |                    0 |                2 |                 0 |          | master              |                      |                0 |                0 |                 0 |                    |                  1 |                   1 |                  0 |        20201206 |          171928 | 1          | 05FA3EA7-6820-466E-9970-FBB1E124CD4B |
| F1D358E6-FF5D-4494-AFF3-5B71465BB05A |         3 | Query Environment           | PowerShell  | $PSVersionTable | Out-File C:\TEMP\PSVersionTable.txt                                                                                                                                                                                                                                  |      40 |                         |                      0 |                   3 |                    0 |                2 |                 0 |          | master              |                      |                0 |                0 |                 0 |                    |                  1 |                   0 |                  0 |        20201206 |          171929 | 1          | 4CE0E8E3-35F2-40CA-A298-1F80097539DB |


Table: syssessions
|   session_id | agent_start_date        |
|-------------:|:------------------------|
|            1 | 2020-10-09 14:32:43.213 |
|            2 | 2020-10-09 15:01:48.383 |
|            3 | 2020-10-12 05:27:02.903 |
|            4 | 2020-10-12 09:25:20.746 |
|            5 | 2020-10-15 17:41:17.866 |
|            6 | 2020-10-15 17:42:08.320 |
|            7 | 2020-10-19 08:23:04.236 |
|            8 | 2020-10-19 20:49:08.126 |
|            9 | 2020-10-24 09:05:09.393 |
|           10 | 2020-10-26 12:51:27.596 |

 


tables: name: sysjobactivity
data: sysjobactivity.csv
name: sysjobhistory
data: sysjobhistory.csv
name: sysjobs
data: sysjobs.csv
name: sysjobsteps
data: sysjobsteps.csv
name: syssessions
data: syssessions.csv
label: sysjobactivity
session_id
syssessions
session_id
sysjobsteps
job_id
sysjobs
job_id
sysjobactivity
job_id
sysjobs
job_id
sysjobhistory
job_id
sysjobs
job_id
output: {
    "joins": [
        {
            "from_table": "sysjobactivity",
            "from_column": "session_id",
            "to_table": "syssessions",
            "to_column": "session_id"
        },
        {
            "from_table": "sysjobsteps",
            "from_column": "job_id",
            "to_table": "sysjobs",
            "to_column": "job_id"
        },
        {
            "from_table": "sysjobactivity",
            "from_column": "job_id",
            "to_table": "sysjobs",
            "to_column": "job_id"
        },
        {
            "from_table": "sysjobhistory",
            "from_column": "job_id",
            "to_table": "sysjobs",
            "to_column": "job_id"
        }
    ]
}