Rename a Column

• Allen Cui-55137

  SSC Guru

  Points: 51650

  More actions

  October 23, 2002 at 8:34 am

  #158851

  Rename a column may impace other objects such as index, constraint, trigger, view and stored procedure. How to list those objects that refer to the column which is going to be renamed?

  Same column name may also be defined in many tables.

• Greg Larsen

  SSC-Insane

  Points: 20961

  More actions

  October 23, 2002 at 8:52 am

  #439239

  you can use the following views to get some of it:

  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

  INFORMATION_SCHEMA.KEY_COLUMN_USAGE

  INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

  I suppose you could also use something like:

  select object_name(id) from syscomments where text like '%<column_name>%'

  to find the stored procedures and triggers that contained a column

  Gregory Larsen, DBA

  If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

  Gregory A. Larsen, MVP

• Simon Sabin

  SSCrazy Eights

  Points: 8142

  More actions

  October 23, 2002 at 10:09 am

  #439246

  YOu can use sysdepends as well.

  This finds all objects dependant on the orderid column of the orders table.

  If you use dynamic SQL this won't pick it up so you will have to use a search in syscomments

  select object_name(d.id)

  from sysdepends d

  join syscolumns c on d.depid = c.id and d.depnumber = c.colid

  where c.id = object_id('orders')

  and c.name = 'orderid'

  Simon Sabin

  Co-author of SQL Server 2000 XML Distilled

  http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

  
  Simon Sabin
  SQL Server MVP
  
  http://sqlblogcasts.com/blogs/simons

• Robert W Marda

  SSChampion

  Points: 13413

  More actions

  October 23, 2002 at 10:42 am

  #439253

  I ran a test on a table with various objects you named and discovered the following:

  The index stays with the column even though you rename it.

  SQL Server won't let you rename a column used by a constraint.

  SQL Server doesn't change triggers, views, nor SP's.

  So I don't think you have to worry about indexes and you'll be forced to remove constraints before you can rename the column. So your main concern will be for triggers, views, and SPs

  Robert W. Marda

  SQL Programmer

  bigdough.com

  The world’s leading capital markets contact database and software platform.

  Robert W. Marda
  Billing and OSS Specialist - SQL Programmer
  MCL Systems