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 '''+'.' +''', '''
+replace(,' ','') +''', ''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(' ',!=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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s