Geek asks and answers

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

Is there any sql statement used to change the name of all columns in UPPER CASE for all tables in the database? MS SQL Server

I have sql to do this, but not sure if it is correct.

  • execute 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

  • copy and execute the result above

+3
source share
4 answers

If you are upgrading an application from SQL Server 2000 to a later version and you are struggling with SQL Server case sensitivity, I would suggest you study SQL Server 2000 compatibility tuning before making drastic changes to the database.

In SQL Server 2008 Management Studio

  • properties
  • Options
  • . Compatibility Level: SQL Server 2000

, .

. , OP SQL Server 2005 "" SQL Server 2005, .

+3

, .

, , 1 2 :

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

.

:

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

.

+2

- .

If you need to do this (and many studies suggest that all uppercase names are distracting from readability), you will have to create new tables with these uppercase names, copy data from the old to the new table, discard old tables, rename new tables and restore everything foreign key relationships.

Is there a good reason for this?

+1
source

Expanding Bravax's answer, you get a list of commands to execute

select 'sp_RENAME ''' + t.name + '.' + QUOTENAME(c.Column_Name) + ''', ''' + UPPER(c.Column_Name) + ''', ''COLUMN'';\n go' As Command
from sys.tables t
INNER JOIN information_schema.columns c ON c.Table_Name = t.Name
ORDER BY t.name

You may need to add intermediate lines if you work in bulk

0
source

More articles:


All Articles