Rename table column names in database

[fiestadonald]

I just converted a database from Oracle to SQL server. I need to rename all table column names. Each column name starts with a "B" (i.e. B02TEMP). I need to remove the "B" from each column name. I can manually edit each table and change each column name in EM, but I would like to create a script I can run and have it done automatically.

How can I automate the process of removing the leading "B" from each column name in 200+ tables?

I am new to SQL server and could use some assistance.

Thank you,
Mark

 

[essa2000]

Dears

I have a stored proc which takes two input parameters

1. Old Column Name
2. New Column Name

This will renames the old name with new name in all the tables in the current database

create procedure col_rename
@varOldcol sysname,
@varNewcol sysname
as
begin
declare @varTablename varchar(40),
@strSQL varchar(150)
print @varOldcol
print @varNewcol
declare tab_col_cur cursor
for
select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = @varOldcol

open tab_col_cur
fetch next from tab_col_cur into @varTablename
while @@fetch_status = 0
begin
set @strSQL= 'exec sp_rename ' + '''' + @varTablename + '.' + @varOldcol + ''''+ ', ' + ''''+@varNewcol +''''+ ' , ' + '''' + 'COLUMN' + ''''
exec (@strSQL)
fetch next from tab_col_cur into @varTablename
end
close tab_col_cur
deallocate tab_col_cur
end

You can alter this stored procedures for your specific requirements. I'll try to post the exact solutions after make editing in the above SP.


Regards,
Essa

 

[AWithers]

I think this can be done by doing an update on table syscolumns but you must be very careful as any views, stored procedures, relationships etc against these tables will fail.

start by running select * from syscolumns where syscolumns.name like 'B%'

--you will need to check that this is the right criteria

--the update should be something like

update syscolumns
set name = right(syscolumns.name,len(syscolumns.name)-1)
where syscolumns.name like 'B%'
--CHange criteria to fit

Hope this helps

Andy

 

[essa2000]

Dear Mark;

I have worked on the previous stored procedure, and edit it for you and this new SP can help you out.

create procedure All_Col_rename

as

begin

Declare @varOldcol varchar(100)
Declare @varNewcol Varchar(100)
declare @varTablename varchar(40), @strSQL varchar(150)

print @varOldcol
print @varNewcol

declare tab_col_cur cursor
for
select COLUMN_NAME , TABLE_NAME from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME like 'B%'

open tab_col_cur

fetch next from tab_col_cur into @varOldcol , @varTablename

while @@fetch_status = 0
begin

-- it will replace first 'B' with ''

SET @varNewcol = Stuff(@varOldcol , 1 , 1, '')

set @strSQL= 'exec sp_rename ' + '''' + @varTablename + '.' + @varOldcol + ''''+ ', ' + ''''+@varNewcol +''''+ ' , ' + '''' + 'COLUMN' + ''''
exec (@strSQL)
fetch next from tab_col_cur into @varOldcol , @varTablename
end
close tab_col_cur
deallocate tab_col_cur
end


But, dear Mark you when you will run this SP it will give you following Caution "Caution: Changing any part of an object name could break scripts and stored procedures."

and confirmation of those columns which are renamed :
The COLUMN was renamed to 'rimaryID'.

and Error Message for those which are referenece and dependencies.

Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
Object '[column name]' cannot be renamed because the object participates in enforced dependencies.

Plesae , be carefull when running these kind of scripts b/c they will change your so many columns.

Please, take backup then test this SP on test database.

Regards,
Muhammad Essa Mughal