Help you to make better use of General SQL Parser

This tool takes advantage of General Sql Parser’s (GSP) power parsing ability to list all table and column relationships, table action mode(select/update/delete/insert/merge) and column location(select list/where clause/join condition) in sql scripts. It’s also able to rename table and column precisely.

SQL can be nested inside stored procedure. There is no need connect to a database instance to get all table and column references as long as the SQL itself is not ambiguous. With provided metadata information, this tool can determine relationship of table and column even if SQL is ambiguous.

Extract table and column names from sql script

SELECT e.last_name,
       e.department_id,
       d.department_name
FROM   employees e
       LEFT OUTER JOIN department d
         ON ( e.department_id = d.department_id );

Table and columns:

Tables:
department
employees

Columns
department.department_id
department.department_name
employees.department_id
employees.last_name

Extract table and column names from ambiguous sql script

select sum(promo_sales)
from (select promo_desc, sum(dollars) as promo_sales
      from promotion natural join sales
      group by promo_desc) as promos
where promo_desc not like 'No%';

Result without providing metadata:

Tables:
promotion
sales

Columns:
missed.dollars
missed.promo_desc

As you can see, column dollars and promo_desc were marked as missed. You can provide metadata by using callback function provided by GSP library. For above sql script, if you provide following metadata:

{"server","db","schema","promotion","promo_desc"},
{"server","db","schema","sales","dollars"}

Then, you will get result generated by this tool like this:

Tables:
promotion
sales

Columns:
promotion.promo_desc
sales.dollars

CRUD report of tables in sql script

insert into sales_reps(id,name)
select employee_id,last_name
from employees
where job_id like '%REP%';

CRUD result:

sstinsert
 sales_reps(tetInsert)
   id(resultColumn)
   name(resultColumn)
 sstselect
  employees(tetSelect)
    employee_id(resultColumn)
    last_name(resultColumn)
    job_id(where)

As you can see, table sales_reps is marked as insert and table employees is marked as Select. Furthermore, location of columns is listed as well.

Give a try of Get Table Column Live Demo

Rename table and column in sql script

You may use text search and replace method to rename table/column in sql script. This only works for simple sql query, for the most real world sql, text search and replace or regex search doesn’t work. Let’s take this sql for example:

SELECT e.name,
       m.name,
       d.name
FROM   employees e
       LEFT OUTER JOIN employees m ON ( e.mgr_id = m.id )
       LEFT OUTER JOIN department d ON ( e.department_id = d.department_id )

If we need to rename employees.name to ename. We can’t just replace all name with ename because it will also change department.name to ename. We also can’t just replace e.name with e.ename, this will make m.name unchanged. The result we need is:

SELECT e.ename,
       m.ename,
       d.name
FROM   employees e
       LEFT OUTER JOIN employees m ON ( e.mgr_id = m.id )
       LEFT OUTER JOIN department d ON ( e.department_id = d.department_id )

This is only achievable by using the power parsing ability of GSP.

Give a try of Rename Table Column Live Demo

How to get this tool

Please download the latest version of General SQL Parser from download page. For Java version, please check runGetTableColumn.java under demos\gettablecolumns\ directory. For .NET version, please check getTableColumn.cs under demoCollections\getTableColumn\ directory.

Data lineage and column impact

Based on the power parsing ability, collecting the data lineage model and analyze column impact from a bunch of sql files is possible. Please check this document for more information.

Detailed explanation about how this demo works

When you are reading source code of this tool, content listed below will help you to make a better understand of how this demo works.

The logic to list all table and column in SQL script is quite simple (source code of this tool is included so you can modify it to fit your own need)

1. Parse SQL script, this tool will quit with error message if it can’t parse SQL script successfully.

2. Iterate all statements recursively including nested subqueries and statements in stored procedure.

3. For each statement, list all tables involved. Table can be base table, subquery, CTE or linked Table(Detailed explanation of those tables please check document alter). Operation such as select/insert/delete/update apply to this table also included.

4. For each table, list all columns belong to this table. Location of this column such as select list, where condition, join condition also included.

Table type

There are several types of table in SQL statement. Table type is important when GSP build reference between column and table.

1. base table, this is the physical table that exists inside database.

select ename from emp;

emp is a base table.

2. subquery, also know as derived table.

select e.employeeName
from (select ename as employeeName from emp) e

(select ename as employeeName from emp) in from clause is treated as a table which type is subquery. column employeeName in select list of main query is linked to this subquery while ename inside subquery is linked to base table: emp.

3. CTE(Common Table Expression)

with venuecopy(vid,vname) as (select venueid,venuename from venue)
select vid from venuecopy

venuecopy is a table which type is CTE. column vid in select list is linked to venuecopy.

4. Linked Table, this is table type defined by GSP.

4.1 deleted, inserted table in SQL Server create trigger statement.

CREATE TRIGGER updEmployeeData 
ON employeeData 
FOR update AS
   IF (COLUMNS_UPDATED() & 14) > 0
      BEGIN
      INSERT INTO auditEmployeeData
         (audit_log_type,
         audit_emp_SSN)
         SELECT 'OLD', 
            del.emp_SSN
         FROM deleted del

      INSERT INTO auditEmployeeData
         (audit_log_type,
         audit_emp_SSN)
         SELECT 'NEW',
            ins.emp_SSN
         FROM inserted ins
   END

deleted, inserted are linked table in this SQL, they linked to based table employeeData .

4.2 table alias used in update statement.

UPDATE   t1
SET   col1 = t1.col2
FROM   mydb.dbo.table1 t1
   JOIN mydb.dbo.table2 t2 
   ON t1.col3 = t2.col3

t1 is a link table which is linked to mydb.dbo.table1.

Note

This tool is available after General SQL Parser .NET version 2.6.3 and Java version 1.6.0.1. For old version of this tool, please check

1. OLD VERSION of Get columns and tables in SQL script (.NET version)

2. OLD VERSION of Get columns and tables in SQL script (Java version)