This is a short post on how to rename a column in a SQL Server table.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I Typo’d a Name
I was testing something recently and created this table:
CREATE TABLE BinaryData
(
BinaryDataID INT NOT NULL
CONSTRAINT BinaryDataPK PRIMARY KEY,
Emoji VARBINARY(16),
UserPic VARBINARY(MAX),
BinaryTexet VARBINARY(250)
)
GO
I didn’t realize at first that the last column was BinaryTexet and not BinaryText. I added data, wrote a few queries, and had done some testing. Using SQL Prompt is great, but it hid my silly mistake. As I went to send this code elsewhere, I realized my mistake.
I could have dropped the table, created a new one with the right name, saved the data or moved it, and done more, but that’s a lot of work. It also might cause me issues with the dependencies from other code. Not this column name, as any dependencies have to be fixed, but I could have other FKs or things that I don’t want to recreate.
I could, I mean I use version control and tools to make refactoring easy, but everything is more complex than renaming this column.
Renames
In SQL Server, we have an sp_rename function to handle this. I had hoped there was a way with ALTER TABLE ALTER COLUMN, but while I can change types, nulls, etc., I can’t change the name.
SQL is such a weird language.
Certainly for SQL Server. PostgreSQL and Oracle have an alter table rename column a to b in the DDL. We have a function. Vote for a cleanup here.
In any case, the syntax is: sp_rename <objname>, <newname>,<objtype>.
For me, the code is easy. I qualify out the old name:
EXEC sp_rename 'dbo.BinaryData.BinaryTexet', 'BinaryText', 'column'
If I try just the column name, even though this is unique in sys.columns, I get an error. I could do just the table and column like this:
EXEC sp_rename 'BinaryData.BinaryTexet', 'BinaryText', 'column'
One really interesting thing is that if I fully qualify things, I definitely don’t get what I want. Be careful here. Another reason why an ALTER TABLE RENAME COLUMN syntax should exist.
After all my experiments, I can fix this:
EXEC sp_rename 'dbo.BinaryData.[dbo.BinaryData.BinaryTexet]', 'BinaryText'
SQL New Blogger
This was a short post I made after realizing I made a mistake. I took about 10 minutes to experiment with renames, and another 5 searching to be sure I couldn’t rename things.
In the experiment, I realized how I could break things, so I captured that mistake (and fixed it), and pointed it out. Showcase how you learn. For bonus points, write a blog where you use AI to help you do this.
