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?

Answer 1

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

Answer 2

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);