Rename a Column in All the Tables - SQL

rename a column in all the tables - SQL

Of course you don't need a cursor for this. You can use sys.columns and sys.objects to generate dynamic sql. Then simply execute it. Once you are satisfied the dynamic sql is what you want feel free to uncomment the last line.

----BE WARNED!!!!----
If you change column names your views, stored procedures, functions etc will all be broken.

declare @CurrentColumnName sysname = 'asdf'
    , @NewColumnName sysname = 'qwer'
    , @SQL nvarchar(MAX) = ''

select @SQL = @SQL + 'EXEC sp_rename ''' + o.name + '.' + c.name + ''', ''' + @NewColumnName + ''', ''COLUMN'';'
from sys.columns c
join sys.objects o on o.object_id = c.object_id
where c.name = @CurrentColumnName

select @SQL

--exec sp_executesql @sql

Rename all columns from all tables with specific column name in PostgreSQL?

If you have superuser privileges you can make the changes in one sweep in the system catalogs:

UPDATE pg_attribute
SET attname = 'location_name'
WHERE attname = 'location';

How can I rename my column in a SQL table?

sp_rename 'TableName.ColumnName', 'NewColumnName', 'COLUMN'

How do I rename a column in a database table using SQL?

On PostgreSQL (and many other RDBMS), you can do it with regular ALTER TABLE statement:

=> SELECT * FROM Test1;
 id | foo | bar 
----+-----+-----
  2 |   1 |   2

=> ALTER TABLE Test1 RENAME COLUMN foo TO baz;
ALTER TABLE

=> SELECT * FROM Test1;
 id | baz | bar 
----+-----+-----
  2 |   1 |   2

Rename column SQL Server 2008

Use sp_rename

EXEC sp_RENAME 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'

See: SQL SERVER – How to Rename a Column Name or Table Name

Documentation: sp_rename (Transact-SQL)

For your case it would be:

EXEC sp_RENAME 'table_name.old_name', 'new_name', 'COLUMN'

Remember to use single quotes to enclose your values.

Renaming the columns of a table columns according to a mapping defined in another table - with MYSQL

I think I understand you want to select a column by name, and the names are strings in your TABLE_MASTER.

You can't do this in a single SQL query, because SQL cannot select a column by using a string expression. There's a difference between a string and an identifier. For example, this selects data from a column by identifier:

SELECT header01 ...

But the following is a string expression (a simple one, which is just a constant value). It returns only a fixed string 'header01', NOT the data from a column of that name:

SELECT 'header01' ...

Likewise, using any other expression in a select-list only selects the value of that expression, NOT the data stored in a column named by the string value of the expression.

Therefore if you want a query to return a dynamic column named by some other variable or expression, you can't do it in the same query where you read that expression. You have to format a new SQL query from the values you read. This is called a dynamic SQL statement (already mentioned by spencer7593, who posted an answer while I was writing my own answer).

You could use your TABLE_MASTER to format a dynamic SQL statement to fetch columns and redefine their alias:

SELECT CONCAT(
  'SELECT ', 
   GROUP_CONCAT(CONCAT(ORIGIN, ' AS ', TARGET)), ', ', 
   QUOTE(MAX(NAME)), ' AS NAME ',
  'FROM TABLE_EXAMPLE'
) INTO @sql
FROM TABLE_MASTER;

The result of this is a string that forms another SELECT statement, this one renames the columns as you want:

SELECT header01 AS header_master01,header02 AS header_master02, 'Paul' AS NAME FROM TABLE_EXAMPLE  

Then you can use the string stored in @sql as a dynamic SQL query.

Here's the procedure that does this:

DELIMITER ;;

CREATE PROCEDURE MyProc()
BEGIN
    SELECT CONCAT(
      'SELECT ', 
       GROUP_CONCAT(CONCAT(ORIGIN, ' AS ', TARGET)), ', ', 
       QUOTE(MAX(NAME)), ' AS NAME ',
      'FROM TABLE_EXAMPLE'
    ) INTO @sql
    FROM TABLE_MASTER;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

DELIMITER ;

Call the procedure, and get the result:

CALL MyProc();

+-----------------+-----------------+------+
| header_master01 | header_master02 | NAME |
+-----------------+-----------------+------+
| data01          | data02          | Paul |
| data11          | data12          | Paul |
+-----------------+-----------------+------+

I have to comment that this is a lot of trouble to go through. I would rather fetch the data as it is in the database, and reformat it in my application code.
Then I wouldn't have to use any dynamic SQL to format the columns.

---

Related Topics