SQL Server Solutions : Change Case of all column headers of a database in one shot

Situations arise when you need to change the letter case of column headers in a database and you fear of regenerating whole database or renaming in a tedious manner of one by one.

However you may apply simple solutions defined below for the situation.

Solution applies to:
SQL Server 2008 R2 and earlier


(a) For Changing to All caps:

SELECT 'EXEC sp_rename ''' + object_name(C.[object_id]) + '.' + C.name +
''', ''' + convert(varchar(500), upper(substring(C.name,1,499))) + ''', ''COLUMN'';'
FROM SYS.OBJECTS O INNER JOIN SYS.COLUMNS C
ON C.[object_id] = O.[object_id]
WHERE IS_MS_SHIPPED != 1 AND TYPE = 'U'

(b) For First Capital letter only:

SELECT 'EXEC sp_rename ''' + object_name(C.[object_id]) + '.' + C.name + 
''', ''' + 
convert(varchar(500), upper(substring(C.name,1,1)) + 
lower(substring(C.name,2,499))) + ''', ''COLUMN'';' 
FROM SYS.OBJECTS O INNER JOIN SYS.COLUMNS C 
ON C.[object_id] = O.[object_id] 
WHERE IS_MS_SHIPPED != 1 AND TYPE = 'U'

You just mention a cursor for situation (a) and (b) as:

declare commands cursor for
/*Here you may write the select statements given above for situations (a) and (b)*/

declare @cmd varchar(max)

open commands
fetch next from commands into @cmd
while @@FETCH_STATUS=0
begin
  exec(@cmd)
  fetch next from commands into @cmd
end

close commands
deallocate commands


For giving title case you directly run function given below:

For Title Case:
CREATE function TitleCase(@initialstring varchar(500)) returns varchar(500) 
as 
begin 
declare @Reset bit; 
declare @Ret varchar(8000); 
declare @i int; 
declare @c char(1); 

select @Reset = 1, @i=1, @Ret = ''; 

while (@i <= len(@initialstring)) 
select @c= substring(@initialstring,@i,1), 
@Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end, 
@Reset = case when @c like '[a-zA-Z]' then 0 else 1 end, 
@i = @i +1 
RETURN @Ret 
end 
go 

grant execute on propercase to public; 

SELECT 'EXEC sp_rename ''' + object_name(C.[object_id]) + '.' + C.name + 
''', ''' + 
dbo.TitleCase(C.name) + ''', ''COLUMN'';' as 'Execute' 
FROM SYS.OBJECTS O INNER JOIN SYS.COLUMNS C 
ON C.[object_id] = O.[object_id] 
WHERE IS_MS_SHIPPED != 1 AND TYPE = 'U'; 


Their is one alternate solution for Title case i.e.:

USE sampleDB
GO
/****** Object: UserDefinedFunction [dbo].[fnUNDERSCORE_SEPERATORtoCapitalizationChange ]  **/
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON
GO 
-- =============================================
-- Description: Takes a string with underscore_separator and returns a string 
with ProperCase
-- Example: select dbo.fnUNDERSCORE_SEPERATORtoProperCase('ADULT_QUAL IF_OSIDU')

-- ============================================= 
CREATE FUNCTION [dbo].[fnUNDERSCORE_SEPERATORtoProperCase] (
@underscore_seperated_string sysname  
) 
RETURNS sysname 
AS  
BEGIN  
DECLARE  
@pos int,
@retval sysname,
@letter char(1),
@upper bit  
SELECT  
@pos = 1
,@retval = ''
WHILE @pos <= LEN(@underscore_seperated_string)
BEGIN
IF ASCII(@letter) = 95
OR @pos = 1
SET @upper = 1
ELSE
SET @upper = 0
SET @letter = CASE @upper
WHEN 1
THEN UPPER(SUBSTRING(@underscore_seperated_string, @pos, 1))  
ELSE LOWER(SUBSTRING(@underscore_seperated_string, @pos, 1))
END
SELECT
@pos = @pos + 1,
@retval = @retval + @letter
END
RETURN (@retval)
END