How to convert column names to upper case in all tables

Question

How to convert column names to upper case in all tables

anilpinnamaneni

SSC-Addicted

Points: 493
More actions
June 18, 2009 at 7:30 am

#138804

Hi All,
I want to convert all the column names to upper case in all the tables and i have a lot of tables and i want to do it automatically.Is there any script that i can use to do it automatically.
Thanks
Kumar

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

CrazyMan SSChampion Points: 12560 More actions · Answer 1

you can use Upper ( Colname) to change the case, then you have to use a loop and then use alter column to change the name of the tables, i am assuming that you are not going to do in the production environment.

MannySingh SSChampion Points: 12465 More actions · Answer 2

if you are using select then a T-sql will be sufficient.

select upper(column_name) from information_schema.columns

where table_name= "your tale name"

renaming the physical name with sp_rename check books online

EG:

sp_rename @objname='DBInfoMax_2.ServerName',@newname='SERVERNAME',@objType='COLUMN'

Where DBInfoMax_2 is the table name and ServerName is the Columns name.

@newname = Uppercase SERVERNAME

ObjType='Column'

Maninder
www.dbanation.com

Dugi SSCoach Points: 17998 More actions · Answer 3

After you read for the sp_rename try this:

SELECT 'EXEC sp_rename

@objname = ''' + TABLE_NAME + '.' + COLUMN_NAME + ''',

@newname = LOWER(''' + COLUMN_NAME + '''),

@objtype = ''COLUMN'''

FROM INFORMATION_SCHEMA.COLUMNS

And the results copy to another page and execute...don't do it in production server, do some test before!

Cheers!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

MannySingh SSChampion Points: 12465 More actions · Answer 4

Or this:

Almost the same as above Query, but this will give you the column names as UPPER CASE in the result set:

select 'sp_rename @objname='+''''+Table_name+'.'+column_name+''''+', @newname= '+upper(''''+column_name+'''')+',@objType='+''''+'COLUMN'+''''

from information_schema.columns

Maninder
www.dbanation.com

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

Just out of curiosity – Why would you want to change the name of all columns to be in upper case?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

anilpinnamaneni SSC-Addicted Points: 493 More actions · Answer 6

Hi All,

Thanks a lot for your suggestions.the reason for this in my qa database all the column names are in upper case and in dev all column names are in lower case.In ssis the package is not taking dynamically the upper and lower case when the ssis packages are pointed to dev it is throwing a error like new metadata required.Thats why i am planning to chnage the column names to upper case in dev so that ssis packages will not have any impact

Before doing that i want to know will be there any effect with changing the column names to upper case.I am not changing ant stored procs,views or triggers i am just changing the column names of the table

Thanks

Kumar