May 4, 2003 at 11:07 am
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
May 6, 2003 at 2:47 pm
To find objects with name > 30:
select *
from sysobjects where len(name) > 30
and name not like 'sp_%' --eliminates system procs as long as u dont use sp_
to find columns > 30
select a.Name, b.Name as ColumnNm
from sysobjects a, syscolumns b
where a.id = b.id
and len(b.name) > 30
and a.name not like 'sp_%'
That should get you started.
Darren
Darren
May 7, 2003 at 1:56 pm
Depending on how many offending columns there are:
I would be inclined to do this:
1) Generate SQL for the entire database. Only grab stored procs, triggers, etc; the things you aren't going to be changing manually.
2) Create your script to update all the object names appropriately. Record the original and updated names as I change them.
3) Open the SQL file, and use find and replace to replace all instances of the old column names with the new. Make sure that you change the names of the procedures and triggers themselves, where needed.
4) Change all 'CREATE PROCEDURE' to 'ALTER PROCEDURE', 'CREATE TRIGGER' to 'ALTER TRIGGER', etc.
5) Run the resulting scripts in Query Analyzer.
It doesn't use T-SQL to do the job, but then this isn't something you'll be doing more than once.
RD Francis
R David Francis
May 8, 2003 at 11:18 am
Thanks so much guys..
This is really good advice and so far it has helped me!
Andrew J. Hahn
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply