Skip to content

Instantly share code, notes, and snippets.

@detaybey
Last active November 18, 2022 22:06
Show Gist options
  • Save detaybey/510dd2b17f7dac8a8c04 to your computer and use it in GitHub Desktop.
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
/// <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
}
}
}
@cukaumunnah
Copy link

How do I run this?

Please assist

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment