sqlsql-serversql-server-2016

Update all column names to be lowercase


I need to rename all columns in SQL Server tables to lowercase. I came up with the code below; however, there's something wrong with the select statement. If I run that by itself, it doesn't return any columns with uppercase characters.

DECLARE @TableName NVARCHAR(128);
DECLARE @OldColumnName NVARCHAR(128);
DECLARE @NewColumnName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);

DECLARE ColumnCursor CURSOR FOR
SELECT 
    t.name AS TableName,
    c.name AS ColumnName
FROM 
    sys.columns c
    INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE
    c.name COLLATE SQL_Latin1_General_CP1_CI_AS <> LOWER(c.name) 
ORDER BY
    t.name, c.name;

OPEN ColumnCursor;

FETCH NEXT FROM ColumnCursor INTO @TableName, @OldColumnName;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @NewColumnName = LOWER(@OldColumnName);

SET @SQL = 'EXEC sp_rename ''' + @TableName + '.' + @OldColumnName + ''', ''' + 
@NewColumnName + ''', ''COLUMN'';';

EXEC sp_executesql @SQL;

FETCH NEXT FROM ColumnCursor INTO @TableName, @OldColumnName;
END;

CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;

Solution

  • You're saying: "compare this string case insensitive to a lower case version of the same string."

    Try SQL_Latin1_General_CP1_CS_AS instead of SQL_Latin1_General_CP1_CI_AS.

    Also, you could always just rename all columns instead of only renaming those that aren't already lower-case.