Need to get identify all tables and columns in a SQL Query

Question

I have a massive task to go through hundreds of SQL scripts and identify the Table and Columns in each query as we are updating our system and some of the columns and tables may be renamed or disappear completely.

Is there any product of script that anyone can recommend that will allow me to do this, taking into consideration that whatever program/script is used will need to take into account alias as well as ensuring that it can differentiate between blocks of select code or union.

I have hundreds of queries being used for reports. I need to go through each query and identify tables and columns that are being used within each query.

Answer 1

not "ready to use" solution, but may be is what You need: http://www.sqlparser.com

description

as I explain in comment, this is framework, it not ready to use solution, but it has very good set of code examples, as well as live-demo.

Live demo

USE [Audit]
GO
ALTER PROCEDURE [dbo].[ReportData3] 
    -- Add the parameters for the stored procedure here
    @PERNR int,
    @PERID int,
    @SNAME int,
    @DateSt date,
    @DateFin date,
    @GROSS_PAY bit

AS
BEGIN
    SET NOCOUNT ON;

IF @GROSS_PAY = 0

   IF @PERNR IS NOT NULL
   SELECT        PERNR, SNAME, WEEK_BEGIN_DATE, PERID, PLSTX, STLX, VDSK1_TEXT, HIRE_DATE, 
                       WEEK_ENDING_DATE, WOSTD, HR_REG, REG_PAY, HR_HOL, HOL_PAY, HR_VAC, VAC_PAY, HR_SIC, SIC_PAY, HR_OVT, OVT_PAY, HR_PRE, PRE_PAY, HR_OTH, 
                       OTH_PAY, HR_TOT_WORKED, HR_STR, STR_PAY, HR_TOT, TOT_PAY, Subtotal, RATR_OF_PAY_HORLY, RATR_OF_PAY_WEEKLY, GROSS_PAY, PAYDT, 
                       AVG_STRAIGHT, AVG_TOTAL, AVG_GROSS, RESULT, SEQNR, TERMDT
   FROM            data_hist
   WHERE WEEK_ENDING_DATE >= @DateSt AND WEEK_ENDING_DATE <= @DateFin and 

-- DELETED 118 rows of code

Result of parsing:

Tables:
data_hist
Fields:
data_hist.AVG_GROSS
data_hist.AVG_STRAIGHT
data_hist.AVG_TOTAL
data_hist.GROSS_PAY
data_hist.HIRE_DATE
data_hist.HOL_PAY
data_hist.HR_HOL
data_hist.HR_OTH
data_hist.HR_OVT
data_hist.HR_PRE
data_hist.HR_REG
data_hist.HR_SIC
data_hist.HR_STR
data_hist.HR_TOT
data_hist.HR_TOT_WORKED
data_hist.HR_VAC
data_hist.OTH_PAY
data_hist.OVT_PAY
data_hist.PAYDT
data_hist.PERID
data_hist.PERNR
data_hist.PLSTX
data_hist.PRE_PAY
data_hist.RATR_OF_PAY_HORLY
data_hist.RATR_OF_PAY_WEEKLY
data_hist.REG_PAY
data_hist.RESULT
data_hist.SEQNR
data_hist.SIC_PAY
data_hist.SNAME
data_hist.STLX
data_hist.STR_PAY
data_hist.Subtotal
data_hist.TERMDT
data_hist.TOT_PAY
data_hist.VAC_PAY
data_hist.VDSK1_TEXT
data_hist.WEEK_BEGIN_DATE
data_hist.WEEK_ENDING_DATE
data_hist.WOSTD

Answer 2

If the queries are in stored procedures, or you can get them into a temporary database as stored procedures then the system SP sp_depends and related catalog views will help. At the very least they will narrow the search.

If the queries are in files a bit of Powershell will generate SPs in no time.

If they're embedded in the application or, heaven forfend, dynamically generated at run time running an application regression test, capturing the submitted SQL and proceeding as above may work.

We have had success with a .Net SQL parsing library. Its name eludes me just now. Should I find it I'll edit it into this answer. You'll still have to pull the SQL from your application, of course.

Answer 3

You state that "some of the columns and tables may be renamed or disappear completely." I would interpret the corollary to that is: most of the columns and tables remain as they were.

Therefore, assuming that assessment is correct, rather than trying to identify everything you should build a test database to make the changes.

Restore a copy of your database and name it [Databasename_RevisionTest]. Then make your changes in the test database.

Once you have changed the tables, views, (and aliases?) then use some software to compare the tables in [Databasename] with [Databasename_RevisionTest] and report the changes.

There are plenty of tools to do the compare. I happen to use Red Gate's SQL Compare, but there are plenty of other products. I even see a 'free' compare tool at http://dbcomparer.com/.

Using such a tool can report all the changes made and could simplify your work by focusing only on what changed.

Of course, you will want to change your code to match the new table and column definitions. One simple, but tedious method is to paste the code (scripts, stored procedures, etc.) into the SSMS Query pane and run the Query / Parse command.

I see that there is a Code Project to parse code at: http://www.codeproject.com/Articles/410081/Parse-Transact-SQL-to-Check-Syntax

I have not used the code project, but perhaps you will find it useful.

EDIT: I had suggested a copy of the database, but since the metadata is all that is needed for your test, perhaps the organization would be willing to create a [Databasename_RevisionTest] database that contains no data.

Answer 4

You can use Gudusoft SQL parser It is great! https://gudusoft.com/sqlflow/#/