Solved

Global Rename / Shorten field names

Posted on 2006-11-11
8
373 Views
Last Modified: 2008-02-01
I have some tables with very long field names. I would like to shorten them to be no longer than 60 chars in length. Is it possible to do this globally? Can the field names be changed in a system table that would reflect the actual table field name?
0
Comment
Question by:ckelsoe
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 17921432
DON'T edit the system tables directly.

Furthermore, if you change field names you will end up breaking dependant objects (such as views, functions and stored procedures). I am afraid that you will have to do this one by one, carefully checking the dependancies.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 17921435
Hi ckelsoe,

I assume that the new name is string upto 60 characters ...
The following sp will give you the rename script

SELECT 'EXEC sp_rename '''+Table_name+'.'+COLUMN_NAME+''','''+LEFT(COLUMN_NAME,60)+''',''COLUMN'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE LEN(COLUMN_NAME) > 60

Cheers!
0
 

Author Comment

by:ckelsoe
ID: 17921495
Hi aneeshattingal

That works to a point. It is possible that the long field name would have the same name at 60 chars, so how could I put an incremental number at the end so that it would be  something like samefieldname1, samefieldname2, etc.
0
 
LVL 1

Assisted Solution

by:Yogeshup
Yogeshup earned 250 total points
ID: 17928838
Maybe this will help...


SELECT 'EXEC sp_rename '''+Table_name+'.'+COLUMN_NAME+''','''+ rtrim(ltrim(LEFT(COLUMN_NAME,60)))

+ (select cast( count(1) as varchar) from INFORMATION_SCHEMA.COLUMNS tmp where tmp.table_name = INFORMATION_SCHEMA.COLUMNS.table_name and LEFT(INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,60) =
LEFT(tmp.COLUMN_NAME,60) and INFORMATION_SCHEMA.COLUMNS.ordinal_position >= tmp.ordinal_position)  +
  ''',''COLUMN'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE LEN(COLUMN_NAME) > 60
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17928984
Yogesh,
I think he is looking for something like this
for example he has tables say Tab1 and Tab2. Let us say both of these contains 5 fields each
TabA (a.....a, a....b ,Col, a....c etc) similar in tab2 also, now what he want is to rename such that

TabA(a....1, a....2,Col, a...3 )

This can be done with cursors  and with temptables / tablevariables

0
 
LVL 1

Expert Comment

by:Yogeshup
ID: 17929493
I thought that is what my query does. What it does is that it searches the columns table for the same columns (first 60 chars only) within the same table and if found, it will add a number based on the order (ordinal_position, which I am assuming is unique for each column within a table). I think what it also might end up doing even if the column name is unique, it will still add a 1 to it. I think that might require a case. I dont think he needs to use cursors. Am I right?

Also, I am hoping that he does not have procedures using these tables else he might have to replace it manually, right?
0
 

Author Comment

by:ckelsoe
ID: 17929577
Thanks for the help.

There are no procedures, views, etc. so renaming the fields will not break anything. The number at the end of the field name can be any number so long as it is unique. I am traveling rignt now and do not have the ability to test Yogesh's example.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17930083
@Yogeshup ,
I am sorry Yogesh, actually I didn't see the 'ORDINAL_POSITION' Part, I also work out in the sameway , but as u have already posted i don't find any necessary to repost it again
:)
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now