CODEWITHSUNDEEP
sqlsql-server
Paul
Paul

Reputation: 43

Add a prefix for each column

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.

Upvotes: 1

Views: 1566

Answers (3)

Suraj Kumar
Suraj Kumar

Reputation: 5643

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

enter image description here

You can find the live demo Live Demo Here

Upvotes: 0

Dumi
Dumi

Reputation: 1434

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

Upvotes: 1

Buchiman
Buchiman

Reputation: 320

You can use sp_rename to rename a column/table.

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

Upvotes: 0

Related Questions