Tag: rename column names in SQL Server

How to remove space in a column names of table in SQL Server

Here is a script to rename the column names to avoid the space(s) in the column name.

create table test_table([column test space] int)

select * From test_table

--Script to do the change in column name

Declare @SQL nvarchar(MAX)=''
Set @SQL =(
select ';EXEC sp_rename '''+ B.name+'.'+a.name +''', '''
+replace(a.name,' ','') +''', ''COLUMN'''  
from sys.columns A
Inner join sys.tables B on A.object_id = B.object_id and  
OBJECTPROPERTY(b.object_id, N'IsUserTable') = 1
 system_type_id in 
(select system_type_id From sys.types ) and charindex(' ',a.name)!=0
FOR XML path(''))

print @SQL
/*exec( @SQL) This has been commented, verify the results and execute*/
--check the column names
select * From test_table
--Cleanup the table
Drop table test_table