My Technical Notes

Thursday, 16 May 2013

SQL Server: Making every column name start with upper case letter

The following SQL code generates a script which you can then execute.


; WITH colData AS (
SELECT t.name AS table_name,


SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name,
UPPER(LEFT(c.name,1))+SUBSTRING(c.name,2,LEN(c.name)) AS up_col_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
)

-- SELECT table_name + '.' + column_name FROM colData
SELECT 'sp_RENAME ' + '''' + table_name + '.[' + column_name + ']' +  '''' + ', ' + '''' + up_col_name + ''', ' + '''' + 'COLUMN' + '''' + CHAR(10) + 'GO' 

FROM colData
WHERE (colData.column_name COLLATE SQL_Latin1_General_Cp1_CS_AS) <>(colData.up_col_name COLLATE SQL_Latin1_General_Cp1_CS_AS)

The above script skips out columns which already start with an upper case letter.

No comments: