Renaming columns, stored procs, triggers

• scranthdaddy

  Mr or Mrs. 500

  Points: 552

  More actions

  May 4, 2003 at 11:05 am

  #101461

  Hi, I need to rename all columns, stored procedures, triggers, and references to each, in order to ensure the names are under 30 characters.

  I will need to:

  1) locate all objects containing names with 30+ characters,

  2) locate all references to those objects (for example, a proc that calls a proc needing a column name change),

  3) write a script that will rename the columns, triggers, and procs (I will need to choose new names that are consistent with the business naming conventions),

  4) write a script to rename all references to the changed items

  I dont believe any of the columns needing change are keys.

  I can use sp_rename to rename the objects, but I need to be sure to catch all of the references. The database is fairly large and there are over 100 stored procs and triggers.

  The purpose of this project is to meet the Oracle under-30-characters naming restriction, in preparation for future migration.

  If anyone has done this before or has any advice, please let me know. I am a newbie to system-table queries and the sp_rename procedure.

  Thanks.

  
  Andrew J. Hahn

• K. Brian Kelley

  SSC Guru

  Points: 114642

  More actions

  May 4, 2003 at 11:12 am

  #456101

  Please don't cross-post. Most readers check all forums.

  The T-SQL version:

  http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=11901

  K. Brian Kelley

  http://www.truthsolutions.com/

  Author: Start to Finish Guide to SQL Server Performance Monitoring

  http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

  K. Brian Kelley
  @kbriankelley