6

If I have a SQL script is there a way to parse and extract the columns and tables referenced in the script into a table like structure :

Script:

Select t1.first, t1.last, t2.car, t2.make, t2.year
from owners t1
left join cars t2
on t1.owner_id = t2.owner_id

Output:

Table   Column
owners  first
owners  last
owners  owner_id
cars    car
cars    make
cars    year
cars    owner_id
Siva Senthil
  • 610
  • 6
  • 22
screechOwl
  • 27,310
  • 61
  • 158
  • 267
  • Are you asking if it's possible or whether there is an easy way? Unless you can limit the input to a simple and strict subset of the grammar, you would have to define the grammar for SELECT and its associated expressions... http://www.h2database.com/html/grammar.html#select – THK Jun 26 '15 at 21:16
  • @THK I guess both. Just seems like this happens somewhere in the sql execution process and was curious if someone had turned it into a library. – screechOwl Jun 26 '15 at 21:20
  • 1
    Somewhat surprisingly, there don't seem to be any standalone parsers widely accepted as authoritative. There are some SO threads about the topic: http://stackoverflow.com/questions/660609/sql-parser-library-for-java. Python has the sqlparse library but it does not validate grammar. – THK Jun 27 '15 at 03:09
  • Please let me know if you found some solution ? – Momog May 28 '17 at 15:33

2 Answers2

2

Old question but interesting so here it goes - turn your script temporarily into a stored procedure forcing SQL Server to map the dependencies and then you can retrieve them by using: 

SELECT referenced_entity_name ,referenced_minor_name FROM sys.dm_sql_referenced_entities('dbo.stp_ObjectsToTrack', 'Object')
Altair Souza
  • 51
  • 2
0

This is what you want in SQL Server:

select t.name as [Table], c.name as [Column]
from sys.columns c
inner join sys.tables t
on c.object_id = t.object_id
Chuck
  • 1,001
  • 1
  • 13
  • 19