Last active
November 18, 2022 22:06
-
-
Save detaybey/510dd2b17f7dac8a8c04 to your computer and use it in GitHub Desktop.
Removes double quotes from Table and Column names on a PostgreSQL server using Dapper
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/// <summary> | |
/// Removes double quotes from Table and Column names. | |
/// By default PgAdmin and other schema generators create names with "Quotes" which is an inconvenience while writing queries. | |
/// This function scans the db and removes quotes from table and column names. | |
/// </summary> | |
public void RemoveDoubleQuotesFromTableAndColumns() | |
{ | |
// get all the table list | |
var tables = db.Query<string>("SELECT table_name FROM information_schema.tables WHERE table_schema='public'").ToList(); | |
foreach (var table in tables) | |
{ | |
// try to remove quotes from table name | |
try | |
{ | |
db.Query(string.Format(@"ALTER TABLE ""{0}"" RENAME TO {0}", table)); | |
} | |
catch (Exception) { /* table may not have quotes */ } | |
// get all the columns under the table | |
var columns = db.Query<string>(string.Format(@"SELECT column_name FROM information_schema.columns where table_name='{0}'", table)).ToList(); | |
foreach (var column in columns) | |
{ | |
try | |
{ | |
db.Query(string.Format(@"ALTER TABLE {0} RENAME COLUMN ""{1}"" TO {1}", table, column)); | |
} | |
catch (Exception) { /* column may not have quotes */ } | |
System.Threading.Thread.Sleep(20); // a little delay for avoiding execution problems | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
How do I run this?
Please assist