how to convert table,column names frm upper to low

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to convert table,column names frm upper to low

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2009-01-02 : 12:51:56

Hello All,

i have a big tables (many columns in it) and all of the column names was in UPPER CASE how can i change all the column names in lower case,

since it is very huge number of columns & also many tables please suggest me other than manually changeing

your ideas highly appreciate

Please Help me

Best Regards
dhani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 12:54:08

didnt understand the intention of doing this. SQL server by default is case insensitive unless you're using a case sensitive collation. Does this have anything to do with your coiding standards?

dhani
Posting Yak Master

132 Posts

Posted - 2009-01-02 : 13:02:28

Hello Visakh,

first of all, thank you for your response,

quote:
SQL server by default is case insensitive unless you're using a case sensitive collation

Yes, I DO accept this but it needs to be work in another tool to create reports

i have to do further reporting purpose i need to import all tables to that tool and reports from the tool

so as per the standards it needs to be all lower case in that tool

Please help me

Best Regards
dhani

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 13:04:42

This will generate the code for you, for all columns and all tables. Add a WHERE clause as necessary.

SELECT 'UPDATE ' + TABLE_NAME + ' SET ' + COLUMN_NAME + ' = LOWER(' + COLUMN_NAME + ')'
FROM INFORMATION_SCHEMA.COLUMNS

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 13:06:11

quote:
Originally posted by tkizer

This will generate the code for you, for all columns and all tables. Add a WHERE clause as necessary.

SELECT 'UPDATE ' + TABLE_NAME + ' SET ' + COLUMN_NAME + ' = LOWER(' + COLUMN_NAME + ')'
FROM INFORMATION_SCHEMA.COLUMNS

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Is it possible to directly update information_schema views?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 13:07:39

I don't understand your question. That's not what my code does.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-02 : 13:10:56

he wants to update column names,

use SP_RENAME for that and generate a SQL like Tara did.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 13:12:52

Ah yes, column names. Just modify the code to use sp_rename instead. It gives you the idea at least.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 13:13:41

quote:
Originally posted by tkizer

I don't understand your question. That's not what my code does.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ah sorry i misunderstood. but how will the posted code change column & table names to lower case? i thought that was what OP asked

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 13:15:01

Yeah...i think sp_rename will do the job

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 13:19:28

This should be close:


SELECT 'EXEC sp_rename 
	@objname = ''' + TABLE_NAME + '.' + COLUMN_NAME + ''', 
	@newname = LOWER(''' + COLUMN_NAME + '''), 
	@objtype = ''COLUMN'''
FROM INFORMATION_SCHEMA.COLUMNS

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-02 : 13:23:23

Hmmm.. I was curious

I tried exec sp_rename 'TableName.ColumnName','columnname','column'
and this works but is a bit manual.

I tried exec sp_rename 'TableName.ColumnName',lower('ColumnName'),'column'
but this didn't work. It spit out a syntax error.

May still need to build a temp table from info schema...

r&r

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 13:27:52

run taras posted code. copy result,paste to another window and execute.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 13:33:46

I have a feeling you can't use LOWER in the parameter value. You might need to use a temp table as revdnrdy mentioned. I didn't test my code, so I haven't a clue if it works.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dhani
Posting Yak Master

132 Posts

Posted - 2009-01-02 : 15:11:58

Hello TaraKizer,

Thank you for your reply,

i ran the below query

quote:
SELECT 'UPDATE ' + TABLE_NAME + ' SET ' + COLUMN_NAME + ' = LOWER(' + COLUMN_NAME + ')'
FROM INFORMATION_SCHEMA.COLUMNS

copy result to new query window and execute.
it says some records updated ......

after that i refresh the DB and check the column names it is still in uppercase

Can you please give details

Best Regards
dhani

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 15:14:15

Did you read the whole thread? You weren't supposed to run that as I misunderstood your original post. You may now have bad data in your database as a result, hopefully you did it in a test environment. Check out my post at 01/02/2009 : 13:19:28 to see what to run. I have a feeling it'll fail though as you probably can't use LOWER right there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources