I have been working with Oracle technology since 2010, and know my way around a database well enough, and do an okay job supporting our E.R.P. system at my job. However, I’m no PL/SQL nor SQL expert. Far from it. And some days I just don’t have the time nor the inclination to “Ask Tom”, so I resort to the tools I am familiar with, no matter how elementary/rudimentary they may be, to get me through the day.

For example: I recently was in a situation where I had to copy three of our prod tables down to a non-prod schema, renaming the tables and each column in the table, for training purposes for a new staff member, so he could follow along with the way the tables were structured in a training manual. I work at Temple University (Go Owls!) which is an Ellucian Banner school, and Banner’s table-naming standards are such that the column headings each have the table name in them. For example, on the SPRIDEN table, every column is named SPRIDEN_[something],  as in SPRIDEN_ID, SPRIDEN_FIRST_NAME … you get the picture. But the training manuals referred to tables in a training schema I no longer had access to,  the  SWRIDEN table, and all column names SWRIDEN_[something]”. Some of these tables have hundreds of columns and I was not inclined to type hundreds of ALTER TABLE” statements into my SQL Developer editor window. Maybe I’m just lazy.

So after creating the new tables by copying the prod versions, via SQL Developer, I could not find a way to globally rename the column headings for the newly copied/renamed tables. If there is such a way to do this, and anyone who may be reading this article knows how, I’d greatly appreciate a comment telling me how to do so. In other words, is there a command where I can say: rename all columns on a table from __ to __?

Instead, I handled this with the tools at hand that I’m familiar with. My Methodology may make you cringe. But remember- this is  “quick and dirty” hack # 1.

First thing: I set my SQL Developer Database preferences for Drag and Drop to format select statements for me, so that I would not have to type out each field name on these very large tables.

Next, I selected all of the columns and copied them: (Ctrl+C) …

… then pasted them onto a new Excel worksheet, in the second column, B (Ctrl+v)

Since each column name is followed by a comma, I globally removed them on the worksheet by selecting the column, and doing a “find & select” then going to the “replace” tab. I just replaced the commas with a space (Literally a hit of the space bar).

Next, I copied that column, with its removed commas, on to another column, skipping one in between. So I went from column “B” to “D”, making sure I  pasted as “values”:

 

---

Now I had identical data in columns “B” and “D”, and needed to replace the old column names with the new. So just like I used “find & select” to replace the commas in column “B” with spaces, I did the same thing in column “D”, replacing the old table name with the new, or in my case, replace SPRIDEN with SWRIDEN:

So far I had:

I was then ready to build the “ALTER TABLE” statements.

In column A1 of the spreadsheet, I entered my alter statement: Alter table SWRIDEN rename column. In C1 put to and in E1, the semicolon to end the SQL statements I’m creating:

To copy the alter statements down to the last row in A that is adjacent to B,  don’t click and drag!  The easiest way to fill a column in Excel is by hovering over the bottom right corner of the cell you want to copy down, until the small square black box appears in that corner, then double click on the small black box:

Fill columns C  “to”  and in E the semicolon

---

 

The alter statements have now been built.  Then I simply copied and pasted them right into SQL Developer and executed:

---

 

I hope this little hack will be useful to others out there who may be new to SQL, and just need a fast way to get something done, with older, more familiar tools. Look for more quick and dirty hacks to come. I’d love to hear some of yours as well!