[dba-SQLServer]Changing a Column Name Globally

create procedure sp_BulkColumnRename (
   @old_column_name varchar(128),
   @new_column_name varchar(128) )
as
select [Exec_Command] =
   'EXEC sp_rename ''[' + o.name + '].[' + 
      @old_column_name + ']'', [' + 
      @new_column_name + '], ''column'''
   from sysobjects o (nolock)
   inner join syscolumns c (nolock)
      on o.id = c.id and o.type = 'U'
   where c.name = @old_column_name
go
exec sp_BulkColumnRename 'AAA', 'BBB'

If you tweek it a little, you will be able to do the whole thing in one
shot.  But I would excersise as much caution as possible while doing
something as drastic and global as this.  I would do a BEGIN TRAN at the
beginning of the resulting script and ROLLBACK at the end of it and see
if I am getting any errors, prior to performing COMMIT.

As per replacing references to the column in views, procedures,
functions, triggers, etc. - I would script those objects and do
Find/Replace.

Again, caution is important, meaning have reliable backups before doing
anything.  I would also script all non-data related objects and
permission assignments and store them in safe place.  ...I hope I am
preaching to the choir here :)

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
Fuller
Sent: Tuesday, October 07, 2003 1:02 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Changing a Column Name Globally


I wrote something very similar using sysobjects and syscolumns and
syscomments, so the "find" part is trivial. The "replace" part is what
I'm hung up on. I want to be able to change the name of column AAA to
BBB and have every dependent object affected. Imagine 500 tables, 1000
views and 1000 sprocs; I want to change AAA to BBB and have everything
continue to work. An opium dream?

A.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
Francisco H Tapia
Sent: Monday, October 06, 2003 8:57 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer]Changing a Column Name Globally


Arthur Fuller wrote:

> Suppose I want to change a column name globally (i.e. in every table,
> every sproc, every view and every UDF). Is there a concise way of 
> doing it? SalesID -> SaleNumber.
> 
> TIA,
> Arthur
> 

Don't know about Sprocs or UDF's but I have an old script from the days 
in SWYNK.com, it's called sp_ColumnHelp, it locates all the views/tables

that a specific column is available and if you add the additional 
paramter even gives back only those that match a specific value.

With some tweaking I'm sure you could adjust it to do what you want for 
the tables/views, but I'm not sure about sprocs and udfs.

I can send you the .sql attachment seperatly if you want it.

-- 
-Francisco


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 9/1/2003
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 9/1/2003
 


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com