Global Rename / Shorten field names

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?
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
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'''

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.
ckelsoeAuthor Commented:
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.
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

YogeshupConnect With a Mentor Commented:
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)  +
Aneesh RetnakaranDatabase AdministratorCommented:
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

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?
ckelsoeAuthor Commented:
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.
Aneesh RetnakaranDatabase AdministratorCommented:
@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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.