Change case of a column name

Question

I have a table in my warehouse that has columns in all uppercase. It scripts out like this:

CREATE TABLE [dbo].[Outlet](
    [OUTLET_KEY] [varchar](10) NOT NULL,
    [OUTLET] [varchar](8) NULL,
    [CITY_CODE] [varchar](4) NULL,
    [OUTLET_NAME] [varchar](25) NULL,
    [COUNTRY_CODE] [varchar](3) NULL,
    [EXTENDED_CATEGORY_CODE] [varchar](2) NULL,
    [PHONE_NUMBER] [varchar](17) NULL
) ON [PRIMARY]

I want to change the case of the columns in the table to a much more readable format

• Outlet_Key
• Outlet
• City_Code
• Outlet_Name
• Country_Code .......

There are about 15 tables and about 200 columns. How do I change the case for all of them?

Answer 1

I would write a small batch program using smo to enumerate all tables and tablecolumns and change the name of the columns.

Answer 2

Here is an easy way to generate the sp_rename commands using just T-SQL, but this doesn't exactly match your naming definition. Getting the first letter after any underscore to be upper cased will require much more elaborate handling (e.g. 26 nested replace(name, '_a', '_A') calls), but perhaps this is a useful start:

SELECT 'EXEC sp_rename ''' 
    + QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) 
    + '.' + QUOTENAME(OBJECT_NAME([object_id])) 
    + '.' + QUOTENAME([name]) + ''', '
    + '''' 
    + QUOTENAME(UPPER(LEFT([name],1))+LOWER(SUBSTRING([name], 2, LEN([name])))) 
    + ''', ''COLUMN'';'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.Outlet');

Run that script in an SSMS query window, then copy the output and run it in a new window.

Okay, here is the version that does the nested REPLACE() calls. It's not pretty, but it works:

USE tempdb;
GO

SET NOCOUNT ON;
GO

CREATE TABLE dbo.Outlet
(
    [OUTLET_KEY] [varchar](10) NOT NULL,
    [OUTLET] [varchar](8) NULL,
    [CITY_CODE] [varchar](4) NULL,
    [OUTLET_NAME] [varchar](25) NULL,
    [COUNTRY_CODE] [varchar](3) NULL,
    [EXTENDED_CATEGORY_CODE] [varchar](2) NULL,
    [PHONE_NUMBER] [varchar](17) NULL,
) ON [PRIMARY];
GO

SELECT 'EXEC sp_rename '''
    + QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) 
    + '.' + QUOTENAME(OBJECT_NAME([object_id])) 
    + '.' + QUOTENAME([name]) + ''', '
    + '''' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
       QUOTENAME(UPPER(LEFT([name],1))+LOWER(SUBSTRING([name], 2, LEN([name]))))
       , '_a', '_A'), '_b', '_B'), '_c', '_C'), '_d', '_D'), '_e', '_E')
       , '_f', '_F'), '_g', '_G'), '_h', '_H'), '_i', '_I'), '_j', '_J')
       , '_k', '_K'), '_l', '_L'), '_m', '_M'), '_n', '_N'), '_o', '_O')
       , '_p', '_P'), '_q', '_Q'), '_r', '_R'), '_s', '_S'), '_t', '_T')
       , '_u', '_U'), '_v', '_V'), '_w', '_W'), '_x', '_X'), '_y', '_Y'), '_z', '_Z')
    + ''',' + '''COLUMN'';'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.Outlet');

GO
DROP TABLE dbo.Outlet;

Answer 3

Give this a shot:

CREATE FUNCTION dbo.Get_Proper_Cased_String
(@my_string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE
     @work_string VARCHAR(MAX),
     @final_string VARCHAR(MAX),
     @position  SMALLINT

    SET @final_string = ''
    SET @position = CHARINDEX(' ', @my_string)

    WHILE (@position > 0)
    BEGIN
     SET @work_string = LEFT(@my_string, @position - 1)

     IF (@work_string <> '')
      SET @final_string = @final_string + ' ' + UPPER(SUBSTRING(@work_string, 1, 1)) + LOWER(SUBSTRING(@work_string, 2, LEN(@work_string) - 1))

     SET @my_string = RIGHT(@my_string, LEN(@my_string) - @position)

     SET @position = CHARINDEX(' ', @my_string)
    END

    /* changed this to LTRIM to remove trailing space that was getting converted to underscore */
    SET @final_string = LTRIM (@final_string + ' ' + UPPER(SUBSTRING(@my_string, 1, 1)) + LOWER(SUBSTRING(@my_string, 2, LEN(@my_string) - 1)))

    RETURN @final_string
END
GO

SELECT
     'EXEC sp_rename ''' + O.name + '.' + C.name + ''', ''' + REPLACE(dbo.Get_Proper_Cased_String(REPLACE(C.name, '_', ' ')), ' ', '_') + ''', ''COLUMN'''
FROM
    sys.objects O
INNER JOIN sys.columns C ON
    C.object_id = O.object_id
WHERE
    O.object_id = OBJECT_ID('<table_name>')

This code assumes that the only non-character value that you have is "_" and that you don't have successive underscores (i.e., MY____COLUMN would give the wrong results).