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.
Do you have a similar script to share with other blog readers? I think we can surely learn from each other here too.
