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

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.

SQL SERVER - Script: Remove Spaces in Column Name in All Tables column-space-01

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

SQL SERVER - Script: Remove Spaces in Column Name in All Tables column-space-02

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.

SQL SERVER - Script: Remove Spaces in Column Name in All Tables column-space-03

Do you have a similar script to share with other blog readers? I think we can surely learn from each other here too.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Quest

SQL Column, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Getting to understand SQL Server Activity Monitor in SSMS
Next Post
Geometry Datatype – Line String Behavior Enhancements

Related Posts

Leave a Reply