{"id":1618,"date":"2015-05-07T01:40:26","date_gmt":"2015-05-07T06:40:26","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=1618"},"modified":"2015-07-25T06:00:39","modified_gmt":"2015-07-25T11:00:39","slug":"listing-all-table-and-column-references","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/listing-all-table-and-column-references\/","title":{"rendered":"Listing all table and column references"},"content":{"rendered":"

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.<\/p>\n

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.<\/p>\n

### Extract table and column names from sql script<\/strong><\/h3>\n

###

###

###

###

###

###

###

###

#####

#####

    \r\nSELECT e.last_name,\r\n       e.department_id,\r\n       d.department_name\r\nFROM   employees e\r\n       LEFT OUTER JOIN department d\r\n         ON ( e.department_id = d.department_id ); \r\n<\/pre>\nTable and columns:<\/p>\n\r\nTables:\r\ndepartment\r\nemployees\r\n\r\nColumns\r\ndepartment.department_id\r\ndepartment.department_name\r\nemployees.department_id\r\nemployees.last_name\r\n<\/pre>\n<\/p>\nExtract table and column names from ambiguous sql script<\/strong><\/h3>\n\r\nselect sum(promo_sales)\r\nfrom (select promo_desc, sum(dollars) as promo_sales\r\n      from promotion natural join sales\r\n      group by promo_desc) as promos\r\nwhere promo_desc not like 'No%';\r\n<\/pre>\nResult without providing metadata:<\/p>\n\r\nTables:\r\npromotion\r\nsales\r\n\r\nColumns:\r\nmissed.dollars\r\nmissed.promo_desc\r\n<\/pre>\nAs 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:<\/p>\n\r\n{\"server\",\"db\",\"schema\",\"promotion\",\"promo_desc\"},\r\n{\"server\",\"db\",\"schema\",\"sales\",\"dollars\"}\r\n<\/pre>\nThen, you will get result generated by this tool like this:<\/p>\n\r\nTables:\r\npromotion\r\nsales\r\n\r\nColumns:\r\npromotion.promo_desc\r\nsales.dollars\r\n<\/pre>\nCRUD report of tables in sql script<\/strong><\/h3>\n\r\ninsert into sales_reps(id,name)\r\nselect employee_id,last_name\r\nfrom employees\r\nwhere job_id like '%REP%';\r\n<\/pre>\nCRUD result:<\/p>\n\r\nsstinsert\r\n sales_reps(tetInsert)\r\n   id(resultColumn)\r\n   name(resultColumn)\r\n sstselect\r\n  employees(tetSelect)\r\n    employee_id(resultColumn)\r\n    last_name(resultColumn)\r\n    job_id(where)\r\n<\/pre>\nAs 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.<\/p>\nGive a try of Get Table Column Live Demo<\/a><\/h3>\n<\/p>\nRename table and column in sql script<\/strong><\/h3>\nYou 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:<\/p>\n\r\nSELECT e.name,\r\n       m.name,\r\n       d.name\r\nFROM   employees e\r\n       LEFT OUTER JOIN employees m ON ( e.mgr_id = m.id )\r\n       LEFT OUTER JOIN department d ON ( e.department_id = d.department_id )\r\n<\/pre>\nIf 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:<\/p>\n\r\nSELECT e.ename,\r\n       m.ename,\r\n       d.name\r\nFROM   employees e\r\n       LEFT OUTER JOIN employees m ON ( e.mgr_id = m.id )\r\n       LEFT OUTER JOIN department d ON ( e.department_id = d.department_id )\r\n<\/pre>\nThis is only achievable by using the power parsing ability of GSP.<\/p>\nGive a try of Rename Table Column Live Demo<\/a><\/h3>\n<\/p>\nHow to get this tool<\/strong><\/h3>\nPlease download the latest version of General SQL Parser from download page<\/a>. For Java version, please check runGetTableColumn.java under demos\\gettablecolumns\\ directory. For .NET version, please check getTableColumn.cs under demoCollections\\getTableColumn\\ directory.<\/p>\nData lineage and column impact<\/strong><\/h3>\nBased 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<\/a> for more information.
    \n<\/p>\nDetailed explanation about how this demo works<\/strong><\/h3>\nWhen you are reading source code of this tool, content listed below will help you to make a better understand of how this demo works.<\/p>\nThe 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)<\/p>\n1. Parse SQL script, this tool will quit with error message if it can’t parse SQL script successfully.<\/p>\n2. Iterate all statements recursively including nested subqueries and statements in stored procedure.<\/p>\n3. 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.<\/p>\n4. For each table, list all columns belong to this table. Location of this column such as select list, where condition, join condition also included.<\/p>\nTable type<\/h5>\nThere are several types of table in SQL statement. Table type is important when GSP build reference between column and table.<\/p>\n1. base table, this is the physical table that exists inside database.<\/p>\n\r\nselect ename from emp;\r\n<\/pre>\nemp is a base table.<\/p>\n2. subquery, also know as derived table.<\/p>\n\r\nselect e.employeeName\r\nfrom (select ename as employeeName from emp) e\r\n<\/pre>\n(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.<\/p>\n3. CTE(Common Table Expression)<\/p>\n\r\nwith venuecopy(vid,vname) as (select venueid,venuename from venue)\r\nselect vid from venuecopy\r\n<\/pre>\nvenuecopy is a table which type is CTE. column vid in select list is linked to venuecopy.<\/p>\n4. Linked Table, this is table type defined by GSP.<\/p>\n4.1 deleted, inserted table in SQL Server create trigger statement.<\/p>\n\r\nCREATE TRIGGER updEmployeeData \r\nON employeeData \r\nFOR update AS\r\n   IF (COLUMNS_UPDATED() & 14) > 0\r\n      BEGIN\r\n      INSERT INTO auditEmployeeData\r\n         (audit_log_type,\r\n         audit_emp_SSN)\r\n         SELECT 'OLD', \r\n            del.emp_SSN\r\n         FROM deleted del\r\n\r\n      INSERT INTO auditEmployeeData\r\n         (audit_log_type,\r\n         audit_emp_SSN)\r\n         SELECT 'NEW',\r\n            ins.emp_SSN\r\n         FROM inserted ins\r\n   END\r\n<\/pre>\ndeleted, inserted are linked table in this SQL, they linked to based table employeeData .<\/p>\n4.2 table alias used in update statement.<\/p>\n\r\nUPDATE   t1\r\nSET   col1 = t1.col2\r\nFROM   mydb.dbo.table1 t1\r\n   JOIN mydb.dbo.table2 t2 \r\n   ON t1.col3 = t2.col3\r\n<\/pre>\nt1 is a link table which is linked to mydb.dbo.table1.<\/p>\nNote<\/h5>\nThis 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<\/p>\n1. OLD VERSION of Get columns and tables in SQL script (.NET version)<\/a><\/p>\n2. OLD VERSION of Get columns and tables in SQL script (Java version)<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"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,\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":235,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nHelp you to make better use of General SQL Parser<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n