How to change all column name to UPPER CASE for all the tables in one database of MS SQL Server?

Question

is there any sql statement used to change all column name to UPPER CASE for all tables in database? MS SQL Server.

I got a sql to do that, but not sure whether it`s correct.

1. run SQL below

   select 'exec sp_rename '''+b.name+'.'+a.name+''','''+UPPER(a.name)+''',''column'''
   from syscolumns a, sysobjects b 
   where a.id=b.id and b.type='U' 
   order by b.name
   

2. copy and execute the result above

Solution 1

If you are upgrading an application from SQL Server 2000 to a later edition, and you are struggeling with SQL Server case sensitivity, I would suggest you look into the SQL Server 2000 compatibility setting before you do drastic changes to the database.

In SQL Server 2008 Management Studio

1. Right-click the database and select properties in the context menu
2. Go to the Options page
3. In the third dropdown from the top. select Compatibility Level: SQL Server 2000

At least that is time consuming.

Edit: Since it appears that OP is upgrading his database from SQL Server 2005 to a "new" database on SQL Server 2005, the above strategy might not be optimal.

Solution 2

I don't believe there is one command to do this.

However you should be able to write a query which does this, using 1 or 2 cursors and a query like:

select t.name As TableName, c.Column_Name
from sys.tables t
INNER JOIN information_schema.columns c ON c.Table_Name = t.Name
ORDER BY t.name 

This should return all table and columns in your database.

Then use:

sp_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'

To rename each column.

Solution 3

Short answer - no.

If you need to do this (and many studies suggest that all upper case names detract from readability), you'll have to generate new tables with these upper case names, copy the data from the old to the new table, drop the old tables, rename the new tables, and re-establish all of the foreign key relationships.

Is there a good reason to do this?

Comments

[outis]

The convention is to reserve upper case for SQL keywords and lower (or mixed) case for DB objects (database, schema, table & column names).

[Ryan]

hi guys, I got a SQL to do this, but not sure if it`s correct.

[Andriy M]

Just to make sure, I would use QUOTENAME(), like in '...' + QUOTENAME(b.name) + '...'. The b.type='U' filter is unnecessary as all object_ids are unique, I understand.

[Ryan]

do I need to regenerate the index? because there are some APP Upgrade issues after I executed the sql.

[Ryan]

ye, as you said, I am upgrading an application, and struggling with the case sensitivity. I am using 2005, and will look into the setting.

[Ryan]

I made a pl/sql statement to do that change when I upgrade on ORACLE, and it works good. So I was thinking it might work good too if I made some changes to it to accommodate sql server, but seems pl/sql is totally different from sql in sqlserver

[Ryan]

Hi Simen, I am upgrading an application on the same case-sensi SQL Server 2005, as the older application using lower case column while later one uses UPPER case, it`s kind of freaking me out by the application designer. :(

[Simen S]

But I think the idea is that if you set the compatibility level to 2000 then Queries to that database are NOT treated case sensitive anymore. This was the SQL Server 2000 behavoiur. By the way: Where can I give a downvote to the developer who decided to go all UPPER CASE on the queries :-)

[Simen S]

OK, If the old application database was also an SQL Server 2005 database, then I guess using the SQL Server 2000 compatibility mode could potentially break some aspect of the database dependent on SQL Server 2005 features. It would be interesting if someone knew more about that.