{"id":350,"date":"2010-07-21T21:43:38","date_gmt":"2010-07-22T02:43:38","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=350"},"modified":"2011-06-08T02:53:02","modified_gmt":"2011-06-08T07:53:02","slug":"rename-table-names-in-sql-script","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/rename-table-names-in-sql-script\/","title":{"rendered":"Rename table names in SQL script"},"content":{"rendered":"

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

\nThis demo renames all tablename to view_tablename, and [tablename] to [view_tablename].\n<\/p>\n

Input SQL query:<\/p>\n

    \r\nSELECT Table1.f1 AS fld1,\r\n       Table2.F2 AS fld2\r\nFROM   Table1\r\n       LEFT JOIN Table2\r\n         ON Table1.f3 = Table2.f3\r\nWHERE  Table2.f5 > 10\r\nORDER  BY Table2.f6; \r\n<\/pre>\nSQL after change table name:<\/p>\n\r\nSELECT view_Table1.f1 AS fld1,\r\n       view_Table2.F2 AS fld2\r\nFROM   view_Table1\r\n       LEFT JOIN view_Table2\r\n         ON view_Table1.f3 = view_Table2.f3\r\nWHERE  view_Table2.f5 > 10\r\nORDER  BY view_Table2.f6 \r\n<\/pre>\n\nDownload this demo: C# version<\/a>, Java version<\/a>\n<\/p>\n\nCode in C# to achieve what we mentioned above.\n<\/p>\n\r\nusing System;\r\nusing System.Collections.Generic;\r\nusing System.Text;\r\nusing System.IO;\r\n\r\nusing gudusoft.gsqlparser;\r\nusing gudusoft.gsqlparser.Units;\r\n\r\nnamespace tablerename\r\n{\r\n    class tablerename\r\n    {\r\n        public static TSourceTokenList maintabletokens;\r\n\r\n        static void Main(string[] args)\r\n        {\r\n\r\n\r\n            TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVMssql);\r\n            maintabletokens = new TSourceTokenList(false);\r\n\r\n            sqlparser.OnTableToken += new TOnTableTokenEvent(OnTableTokenHandler);\r\n\r\n            sqlparser.Sqlfilename = args[0];\r\n            int i = sqlparser.Parse();\r\n            if (i == 0)\r\n            {\r\n                foreach (TSourceToken st in maintabletokens)\r\n                { \/\/ modify table name in from clause here\r\n\r\n                    if (st.AsText.StartsWith(\"\\\"\"))\r\n                    {\r\n                        st.AsText = \"\\\"\" + \"view_\" + st.AsText.Substring(1);\r\n                    }\r\n                    else if (st.AsText.StartsWith(\"[\"))\r\n                    {\r\n                        st.AsText = \"[\" + \"view_\" + st.AsText.Substring(1);\r\n                    }\r\n                    else\r\n                    {\r\n                        st.AsText = \"view_\" + st.AsText;\r\n                    }\r\n                }\r\n\r\n                for (int j = 0; j < sqlparser.SqlStatements.Count(); j++)\r\n                {\r\n                    Console.WriteLine(sqlparser.SqlStatements[j].AsText);\r\n                    Console.WriteLine(\"\");\r\n                }\r\n            }\r\n            else\r\n                Console.WriteLine(sqlparser.ErrorMessages);\r\n        }\r\n\r\n\r\n\r\n        static void OnTableTokenHandler(object o, gudusoft.gsqlparser.TSourceToken st, gudusoft.gsqlparser.TCustomSqlStatement stmt)\r\n        {\r\n            \/\/table in this event is constructed as an TLzTable object, usually it's main point where this table occurs in statement\r\n            if ((st.DBObjType == TDBObjType.ttObjTable) )\r\n            {\r\n                if (o is TLzTable)\r\n                { \/\/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.\r\n                    maintabletokens.Add(st);\r\n                }\r\n                else\r\n                {\r\n                    if (st.AsText.StartsWith(\"\\\"\"))\r\n                    {\r\n                        st.AsText = \"\\\"\" + \"view_\" + st.AsText.Substring(1);\r\n                    }\r\n                    else if (st.AsText.StartsWith(\"[\"))\r\n                    {\r\n                        st.AsText = \"[\" + \"view_\" + st.AsText.Substring(1);\r\n                    }\r\n                    else\r\n                    {\r\n                        st.AsText = \"view_\" + st.AsText;\r\n                    }\r\n                }\r\n            }\r\n\r\n        }   \r\n    }\r\n}\r\n<\/pre>\nDownload this demo: C# version<\/a>, Java version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"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 =\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