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