Convert Table name from lower case to upper case

Question

The script below shows an example query for data that have been converted from lower case, but it only changed the data on one column in the table.

Use MYF601T
Go

UPDATE ROAD_LINE
SET NAM = UPPER(NAM)

However, the following script that I'm trying to write is to convert all on all columns on all tables, but the result generated with errors.

Use MYF601T
Go

UPDATE INFORMATION_SCHEMA.TABLES
SET INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = UPPER(INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA

How to do this for all tables and all columns inside?

`INFORMATION_SCHEMA` are **system-level** catalog views - you **CANNOT** directly manipulate those! What are you *really* trying to do here?? — marc_s, Sep 03 '15 at 08:08
_"the result generated with errors"_ if you write something like this always include the error message — Tim Schmelter, Sep 03 '15 at 08:13
No, its just that i want to change all data that in lower case to uppercase. — RehanNoradibah, Sep 03 '15 at 08:18
You won't achieve this way you try. You probably need Dynamic-SQL built based on `INFORMATION_SCHEMA` which pulls table names and columns, build update statement and execute it — Lukasz Szozda, Sep 03 '15 at 08:20

Julien Vavasseur · Answer 1 · 2015-09-03T09:23:23.173

If for whather reasons you want to convert your tables names to Upper case, you can:

use a cursor that will select the tables you want to rename
loop through the table list
rename it using sp_rename or update it

Note that you have to update the Select in the cursor to suit your needs (select column or table name you want, ...)

This will rename tables:

declare @TABLE_NAME sysname, @TABLE_SCHEMA sysname
declare @TABLE sysname, @newName sysname
declare table_cursor Cursor
    For Select TABLE_NAME, TABLE_SCHEMA From INFORMATION_SCHEMA.TABLES Where TABLE_NAME like 'xyz%' -- and TABLE_SCHEMA like ...

open table_cursor
Fetch Next From table_cursor Into @TABLE_NAME, @TABLE_SCHEMA;

While @@FETCH_STATUS = 0
Begin
    Set @TABLE = quotename(UPPER(@TABLE_SCHEMA)) + '.' + quotename(UPPER(@TABLE_NAME))
    Set @newName = UPPER(@TABLE_NAME)
    print 'rename ' + @TABLE + ' to ' + @newName
    -- uncomment next like if you really want to rename them
    --exec sp_rename @TABLE, @newName
    Fetch Next From table_cursor Into @TABLE_NAME, @TABLE_SCHEMA;
End

Close table_cursor
Deallocate table_cursor

If you want to update all columns xyz in table zyx, you can use this:

declare @TABLE_NAME sysname, @TABLE_SCHEMA sysname, @COLUMN_NAME sysname
declare @TABLE sysname, @sql nvarchar(max)
declare table_cursor Cursor
    For Select TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS 
    Where COLUMN_NAME like 'xxx' -- and data_type  '' ... and TABLE_NAME like 'xyz%' ...  and TABLE_SCHEMA like ...

open table_cursor
Fetch Next From table_cursor Into @TABLE_NAME, @TABLE_SCHEMA, @COLUMN_NAME;

While @@FETCH_STATUS = 0
Begin
    Set @TABLE = quotename(@TABLE_SCHEMA) + '.' + quotename(@TABLE_NAME)
    set @sql = 'Update ' + @TABLE + ' set ' + @COLUMN_NAME + ' = UPPER(' + @COLUMN_NAME + ')'
    print @sql
    -- uncomment next like if you really want to execute them
    --exec sp_executesql @sql
    Fetch Next From table_cursor Into @TABLE_NAME, @TABLE_SCHEMA, @COLUMN_NAME;
End

Close table_cursor
Deallocate table_cursor

Read what OP want to achieve, he wants to UPPERCASE data in table NOT table/column name — Lukasz Szozda, Sep 03 '15 at 09:10
thanks but not so obvious aside from one comment. I added a second query and they answer both type of question. — Julien Vavasseur, Sep 03 '15 at 09:24
The first script work fine... But the second modify THE CONTENT of the column. Not their name — Madlozoz, Dec 04 '17 at 10:55

Ullas · Answer 2 · 2015-09-06T04:52:14.453

Use a dynamic query to update all the column content to upper case.

Query

declare @query varchar(max)

select @query =
stuff
(
    (
        select ';update ' + table_name + ' ' +
        'set ' + column_name + ' = upper(' + column_name + ')'
        from information_schema.columns
        where table_name = 'ROAD_LINE'
        order by table_name,column_name
        for xml path('')
     )
, 1, 1, '')

execute(@query);

Convert Table name from lower case to upper case

2 Answers2