Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

# Listing all table and column references

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)