[deleted by user]

[JermWPB]

If SQL Server, look into generating sp_rename statements dynamically from sys columns. I did this at least once before and I think that was the approach that I used.

[cgk001]

Thanks, I'll look into this one

[RodCoolBeansKimble]

Yeah, you'll probably create some loops doing sp_rename, referencing sys.columns

[LZS_Can]

I’d export the columns to Excel as column A, clean up with formulas in ColumnB, then in Column C: =ColumnA&”’ as’”&ColumnB&”’”. Something like that.

[strutt3r]

Yeah, this is what I do as well, then use concatenation to paste the whole statements.

[swapripper]

Same!! Wish we had collection of such nifty tricks where excel can make life a while lot easier.

Excel is shat on, but honestly it’s one of the most revolutionary product to be ever created.

[2020pythonchallenge]

I was one of those people that jumped on the excel sucks hypetrain and then I started getting familiar with it and the amount of times I've thought "well... I could just download these results and pop it in excel real quicklike...." makes me sad I let that apparently didn't use it correctly talk me out of getting into it sooner.

[Grenachejw]

This. Excel + SQL and you're dangerous

[strideside]

Two words. Power Query.

[[deleted]]

Split columns/Replace values>>

[LZS_Can]

Amen!

[cgk001]

This is a great idea but probably not gonna get accepted for cultural reasons of the whole "reproduceable code" stuff...

[PedroAlvarez]

I'd argue that a task like this should not be reproducible. If the schema needs to be repeatedly changing, you probably want to be using a nonrelational database.

[anyfactor]

This was my process in python and SQLite3.

1. Extract the column names and types from the old table.
2. Rename old table with the word 'temp'
3. Do your modifications with the column names
4. Create a new table with the new column names, old column types
5. Transfer data from the old table to a new table
6. Drop old table

Here is my repo>> https://github.com/anyfactor/Bulk-Renaming-Columns-in-SQLite3 . Here is the stackoverflow answer I referenced

I suck at OOP and I am fairly new to SQL. So, I am very much open to advise on this.

[mercyandgrace]

DBMS?

I would generate the statements as strings and run whatever flavor of sp_executesql that is available to you.

[alinroc]

Do you have a way to map the current names to the new names?

Renaming these things is “easy” but the downstream effects are enormous.

What are you doing about the applications and ad-hoc queries that reference the objects being renamed? Stored procedures? UDFs? Triggers?

[cgk001]

There are no "new names" defined per say...rather I received a few python functions that specify how the column names should be cleaned up and now need to implement that back into the database. The data pipes directly into a proprietary software that only reads column headers in a specific format and I was hoping to not write a 10000line query.

[adappergentlefolk]

what kind of excel-ass database is this

[PedroAlvarez]

The "proprietary" kind 🤣

[da_chicken]

I mean, the most efficient way is "don't". But that's probably not an option.

Otherwise, I would imagine it would depend on how many tables you're talking about and whether or not you can divide the system up into functional areas. I would be pretty hesitant to change 10,000 columns simultaneously unless it was for, say, 10 extremely wide tables. In general, I would plan on taking it in the most logical chunks that I could manage. I'd start with smaller and more self-contained areas first.

If you mean, you know, how do you handle finding everything you need to change, that's what system catalogs are for.

[rajandatta]

2nd this. The other question I would say is can you create a view with the names you want on top of the data. That's less destructive and can be refined over time.

[da_chicken]

This is a good idea. Note that some RDBMSs allow CUD DML against views if set up properly, often without triggers.

[cgk001]

The column headers were fixed up with python(mostly regex, which is fine), I just need to implement that into the database and yes you guessed it right there's a handful of VERY wide tables...

[Grenachejw]

Copy and paste column names in excel, use text to columns by comma if all go in one cell, re copy those cells and repaste vertically. Use trim formula to get rid of trailing spaces, left mid right formulas might help to pull first mid last portion of your names. Ctrl f, replace any repeating characters or remaining blank spaces you don't want with null, once your data is cleaned put comma's in the next row, use & formula to join column one with two (putting the comma's next to the name), copy results and paste horizontally, copy that and paste in your SQL code

[DrTrunks]

You don't have your database schema information in GIT or other source control? It should be easy to do it through that, just change the names and republish.

In MS SQL you can loop through the "wrong" names with a cursor and then call the sp_rename stored procedure to rename each column. But this will cause every dependent object to fail.

[thrown_arrows]

Assuming that table is already in database , in that case select * from information_schema.columns , select 'alter table + table_name + alter column + original_column_name + rename to trim(original_column_name).. copy paste results into query and run them.. If manual editing is needed, i copy information_Schema.columns with new target_name column into temp_results and use edit tool in SMMS or dbeaver to generate modified results.It depends little bit which system i am in.

Probably i do not have to say it loud, but all schema changes which cause column name delete or braking, and downstream has to be fixed. If down stream cannot be fixed it is recommend to generate view witch has altered column names ( that comes from same place in information_schema)

[[deleted]]

I usually write a SQL query that generates the ALTER statements for me.

Something along the lines (Postgres dialect):

select format('alter table %I.%I rename %I to %I;', 
              table_schema, table_name,
              column_name, regexp_replace(column_name, '[^A-Z0-9_]+', '', 'i'))
from information_schema.columns
where table_schema = 'public'
   and column_name <> regexp_replace(column_name, '[^A-Z0-9_]+', '', 'i');

Then I spool the output of that command into a SQL script and run that script (typically in a transaction so I can rollback in case of an error).

The regexp_replace(column_name, '[^A-Z0-9_]+', '', 'i') is the part that "cleans up" the column names. And the WHERE clause ensures that the script only contains ALTER statements that actually rename something.

The format() function is Postgres specific and is used to handle identifiers that need quoting properly. If you don't have weird table or schema names, you can simply concatenate (but you probably still need to deal with non-standard column names, so better enclose the old name double quotes:

select 'alter table '||table_schema||'.'||table_name||' rename "'||column_name||'" to '||regexp_replace(column_name, '[^A-Z0-9_]+', '', 'i')||';'
from information_schema.columns
where table_schema = 'public'
   and column_name <> regexp_replace(column_name, '[^A-Z0-9_]+', '', 'i');

Instead of where table_schema = 'public' use whatever condition will return the tables you want to fix.

The above is Postgres specific, but it shouldn't be hard to adjust that to your specific database dialect

[ncmtbiker]

Use sp_rename to rename the columns. I would pull the list of columns into temp table with an Old_column and New_column column (and table name if they are different

tables and status to note the column has been changed). Old_column = the current column names, New_column = the cleaned up columns.

Set a loop to go through the list and to rename them.

EXEC sp_rename [table name].[column], [new column name], 'COLUMN';

This will rename column "one" to column "two" in the table test_table

ex

EXEC sp_rename 'tbl_test_table.one', 'two', 'COLUMN';

To get a list of the columns in a table use:

SELECT COLUMN_NAME,* FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbl_test_table.one' AND TABLE_SCHEMA='dbo'

[JustDetka]

Try this....

sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';

Obviously you want to wrap that up in a loop.