[dba-SQLServer]Changing a Column Name Globally

Here's an update (1.0.1b) for the previous post:

alter procedure dbo.sp_BulkColumnRename (
   @old_column_name varchar(128),
   @new_column_name varchar(128) )
as
   declare @id int, @exec varchar(8000)
   declare @tbl table (
      [id] int identity(1,1) not null primary key clustered, contents
varchar(255) not null)
   insert @tbl (contents)
      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
   begin tran
   select @id = min([id]) from @tbl
   while @id is not null begin
      select @exec = contents from @tbl where [id] = @id
      exec (@exec)
      if @@error != 0 begin
         set @exec = 'Failed to perform ' + @exec
         raiserror (@exec, 15, 1)
         rollback tran
         return (1)
      end
      select @id = min([id]) from @tbl where [id] > @id
   end
   commit tran
go

-----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