SQL SERVER – Script: Remove Spaces in Column Name in All Tables

7 years ago

Long time ago I have written blog to rename column name.

SQL SERVER – How to Rename a Column Name or Table Name

One of the reader asked me if I can provide a script to remove space from all column names for all tables in a database? My first reaction was – “Are you sure you want to do that? There are chances that after changing the name of the column, the application code might break. If the table has been referred in stored procedure or functions or other objects”

He asked – “Are there any other methods?”. My answer was – One long method would be to import the tables from “problem” database into “staging” database and then insert into your actual tables by mapping the tables correctly where your actual table would not have the space.

I have written the script and share with him.

 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
WHERE  system_type_id IN (SELECT system_type_id
FROM   sys.types)
AND CHARINDEX(' ', a.NAME) <> 0

Above script would NOT make the change, but provide a script which can be verified before executing. This is a good practice to verify the objects and scripts rather than executing it directly. Here is the test run of the script. I have created database and a few tables which have space between columns.

CREATE DATABASE SpaceRemoveDB
GO
USE SpaceRemoveDB
GO
CREATE TABLE [dbo].[AWBuildVersion] (
[SystemInformationID] [tinyint] IDENTITY(1, 1) NOT NULL
,[Database Version] [nvarchar](25) NOT NULL
,[VersionDate] [datetime] NOT NULL
,[ModifiedDate] [datetime] NOT NULL
,[NewCOlumn] [nchar](10) NULL
,[c2] [int] NULL
,CONSTRAINT [PK_AWBuildVersion_SystemInformationID] PRIMARY KEY CLUSTERED ([SystemInformationID] ASC)
)
GO
CREATE TABLE [dbo].[Employee] (
[First name] [varchar](100) NULL
,[Last Name] [varchar](100) NULL
) ON [PRIMARY]
GO

Here is the database, table and columns in SSMS. I have highlighted the column which have spaces.

Now, we can run the script provided earlier to test.

As expected, the script is showing three columns from two tables which has space. Output can be run after verification.

Once we run below, the goal is achieved.

EXEC sp_RENAME 'AWBuildVersion.Database Version', 'DatabaseVersion', 'COLUMN'
EXEC sp_RENAME 'Employee.First name', 'Firstname', 'COLUMN'
EXEC sp_RENAME 'Employee.Last Name', 'LastName', 'COLUMN'

We would get below warning three times (one for each sp_rename)

Caution: Changing any part of an object name could break scripts and stored procedures.

And here is the SSMS after running script.