Add a prefix for each column

Question

I have a table with a lot of columns and I would like to add the prefix ABC_ for every columns.

I tried the below query :

    SELECT syscolumns.name as old_column_name, 'ABC_' + syscolumns.name as new_column_name
    FROM sysobjects 
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    WHERE sysobjects.name = 'ABCtable'
    ORDER BY sysobjects.name,syscolumns.colid

When I execute above query I got all my columns with the prefix ABC_ but in my table nothing has changed.

You have just done a regular select statement and not told it to update anything. — arahman, Jan 08 '19 at 09:03
Possible duplicate of [Rename column SQL Server 2008](https://stackoverflow.com/questions/16296622/rename-column-sql-server-2008) — P.Salmon, Jan 08 '19 at 09:05
Why/how is adding `ABC_` onto the start of all of the column names going to *help* you here? You'll still have the same large amount of columns and you've now worsened e.g. intellisense. — Damien_The_Unbeliever, Jan 08 '19 at 09:07
No I have to do this to realize a jointure with an other table. It is not a duplicate because in my cas I want to add a prefix for each columns not to rename one column. — Paul, Jan 08 '19 at 09:11
Have you considered using table aliases to disambiguate column names from different tables and column aliases to uniquely label them? — P.Salmon, Jan 08 '19 at 09:16
Request you to up vote my answer if you found right and helpful. — Suraj Kumar, Jan 08 '19 at 10:15

score 1 · Accepted Answer · answered Jan 08 '19 at 09:53

You can try something like this.

    DECLARE @Colums AS TABLE(IndexNo INT IDENTITY(1,1), OldCol VARCHAR(MAX), NewCol VARCHAR(MAX))
    INSERT INTO @Colums
    SELECT syscolumns.name as old_column_name, 'ABC_' + syscolumns.name as new_column_name
    FROM sysobjects 
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    WHERE sysobjects.name = 'ABCtable'
    ORDER BY sysobjects.name,syscolumns.colid


    DECLARE @I INT=1;
    WHILE EXISTS(SELECT * FROM @Colums WHERE IndexNo=@I)
    BEGIN
        DECLARE @OldCol VARCHAR(MAX)='', @NewCol VARCHAR(MAX)=''
        SELECT @OldCol='ABCtable.'+OldCol, @NewCol=NewCol FROM @Colums WHERE IndexNo = @I

        EXEC sp_rename @OldCol, @NewCol, 'COLUMN'

        SET @I = @I+1;
    END

It is exactly what I wanted ! Thank you very much ! – Paul Jan 08 '19 at 09:59 — Paul, Jan 08 '19 at 09:59

score 0 · Answer 2 · answered Jan 08 '19 at 09:13

0

You can use sp_rename to rename a column/table.

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

answered Jan 08 '19 at 09:13

Buchiman

320
5
18

Thank you but I have a lot of columns not just one :/ – Paul Jan 08 '19 at 09:18
Just Copy and Paste the query and then rename it. You can Exec it at the Same time. – Buchiman Jan 08 '19 at 09:26
I have 10 000 columns so it is not the best idea ... :/ – Paul Jan 08 '19 at 09:37
1

10 000 columns?!? Seems like your db really needs to be redesigned. – jarlh Jan 08 '19 at 09:48
also not possible unless using sparse columns – Martin Smith Jan 08 '19 at 09:55

Suraj Kumar · Answer 3 · 2019-01-08T10:14:33.317

You can create a cursor as shown below. Please find the below query.

create table studInfo (Id int, EmpCode Varchar(20), Name Varchar(20))
insert into studInfo values (1,'1001', 'A'),(2, '1002','B'), (3, '1003','C'), (4,'1004', 'D'), (5,'1005', 'E')

select * from studInfo

Declare @OldSQLcolName Varchar(50)
Declare @NewColName Varchar(50)

DECLARE RenameCursor CURSOR FOR
SELECT 'studInfo.' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'studInfo'

OPEN RenameCursor;
FETCH NEXT FROM RenameCursor INTO @OldSQLcolName;

WHILE @@FETCH_STATUS = 0
   BEGIN      
      set @NewColName = 'ABC_' + Replace(@OldSQLcolName,'studInfo.','')
      --select @OldSQLcolName, @NewColName

      EXEC sp_rename @OldSQLcolName, @NewColName, 'COLUMN'
      FETCH NEXT FROM RenameCursor INTO @OldSQLcolName;   
   END

CLOSE RenameCursor
DEALLOCATE RenameCursor

select * from studInfo
drop table studInfo

The output before and after updated column name is as shown below

You can find the live demo Live Demo Here

Add a prefix for each column

3 Answers3