2

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.

  • Maybe the Antlr project can help you? This looks interesting. – Vérace Nov 18 '15 at 19:31
4

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.

4

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
  • Thanks a_vlad, yes this would appear to do what I need it to do. I've downloaded the free version, it doesn't look at easy to use but it if it does what it says, it will be perfect – Easy987us Nov 17 '15 at 15:14
  • not sure it necessary in this case. I found it few month ago when try resolve other task - in my work some time need visualise complicate query without build structure. As I wrote - this is not "ready to use" solution. You must switch brain on and use framework in Your own application. But it work as requested in question - 107.170.101.241:8080/getTableColumn – a_vlad Nov 19 '15 at 8:41
2

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.

  • Unfortunately I am just a report writer and do not have access to copy databases etc. The company I am working for us massive and I dont think that this will be an option. My remit at present is to go through all SQL code, find out all tables and columns and then look up against a master list of about 200 table/columns that are changing and then report back to a change committee. Ideally I would like to stick to this. If its not possible, I will have to provide alternatives, of which yours would be one :) – Easy987us Nov 17 '15 at 14:27
  • Well, perhaps looking through 200 tables may not be too difficult. Likely you will need to be alert to some columns that across the database that have the same name, but different purposes. This can be a complication to updating the code, but again it is doable. – RLF Nov 17 '15 at 14:35
0

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

enter image description here

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.