Rename table names in SQL script

Rename table name in SQL query is one of the most important refactor features you need when you manage your SQL scripts. This demo illustrates how to rename table names in SQL query, you can enhance this demo to meet your own requirement.

This demo renames all tablename to view_tablename, and [tablename] to [view_tablename].

Input SQL query:

SELECT Table1.f1 AS fld1,
       Table2.F2 AS fld2
FROM   Table1
       LEFT JOIN Table2
         ON Table1.f3 = Table2.f3
WHERE  Table2.f5 > 10
ORDER  BY Table2.f6; 

SQL after change table name:

SELECT view_Table1.f1 AS fld1,
       view_Table2.F2 AS fld2
FROM   view_Table1
       LEFT JOIN view_Table2
         ON view_Table1.f3 = view_Table2.f3
WHERE  view_Table2.f5 > 10
ORDER  BY view_Table2.f6 

Download this demo: C# version, Java version

Code in C# to achieve what we mentioned above.

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;

using gudusoft.gsqlparser;
using gudusoft.gsqlparser.Units;

namespace tablerename
{
    class tablerename
    {
        public static TSourceTokenList maintabletokens;

        static void Main(string[] args)
        {


            TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVMssql);
            maintabletokens = new TSourceTokenList(false);

            sqlparser.OnTableToken += new TOnTableTokenEvent(OnTableTokenHandler);

            sqlparser.Sqlfilename = args[0];
            int i = sqlparser.Parse();
            if (i == 0)
            {
                foreach (TSourceToken st in maintabletokens)
                { // modify table name in from clause here

                    if (st.AsText.StartsWith("\""))
                    {
                        st.AsText = "\"" + "view_" + st.AsText.Substring(1);
                    }
                    else if (st.AsText.StartsWith("["))
                    {
                        st.AsText = "[" + "view_" + st.AsText.Substring(1);
                    }
                    else
                    {
                        st.AsText = "view_" + st.AsText;
                    }
                }

                for (int j = 0; j < sqlparser.SqlStatements.Count(); j++)
                {
                    Console.WriteLine(sqlparser.SqlStatements[j].AsText);
                    Console.WriteLine("");
                }
            }
            else
                Console.WriteLine(sqlparser.ErrorMessages);
        }



        static void OnTableTokenHandler(object o, gudusoft.gsqlparser.TSourceToken st, gudusoft.gsqlparser.TCustomSqlStatement stmt)
        {
            //table in this event is constructed as an TLzTable object, usually it's main point where this table occurs in statement
            if ((st.DBObjType == TDBObjType.ttObjTable) )
            {
                if (o is TLzTable)
                { //don't rename table in from clause, otherwise, same table name  in other clause ( such as where clause )may not link to it correctly while parsing.
                    maintabletokens.Add(st);
                }
                else
                {
                    if (st.AsText.StartsWith("\""))
                    {
                        st.AsText = "\"" + "view_" + st.AsText.Substring(1);
                    }
                    else if (st.AsText.StartsWith("["))
                    {
                        st.AsText = "[" + "view_" + st.AsText.Substring(1);
                    }
                    else
                    {
                        st.AsText = "view_" + st.AsText;
                    }
                }
            }

        }   
    }
}

Download this demo: C# version, Java version