January 19, 2012

Sql stored procedure to change column name from lower case to upper in table

This is a Sql stored procedure which can be used to change column name from lower case to upper in tables. Its simple to use and understand.

 DECLARE @count INT,  
 @script NVARCHAR(1000),  
 @column_name_old VARCHAR(100),  
 @column_name_new VARCHAR(100)  
 DECLARE @table TABLE  
 (nid INT IDENTITY(1,1),  
 column_name VARCHAR(1000))  
 INSERT INTO @table(column_name)  
 SELECT name  
 FROM syscolumns  
 WHERE id = OBJECT_ID('test_column_change')  
 SELECT @count= count(1)  
 FROM @table  
 WHILE(@count >=1)  
 BEGIN  
 SELECT @column_name_old = column_name  
 FROM @table  
 WHERE nid = @count  
 SELECT @column_name_new = UPPER(@column_name_old)  
 SELECT @Script ='sp_rename ''test_column_change.'+@column_name_old+'''  
 , '''+@column_name_new+''' , ''COLUMN'''  
 --select @script  
 EXEC (@Script)  
 SELECT @Count = @count-1  
 END