Change column name and data type in Sql Server

How to change column name and data type of a column cannot be done in one statement but first we need to change the column name and then data type, In this post we will check many other things like how to change data type of a column, how to drop a column, how to add a new column, how to make a column not nullable etc.

So first we will see how to change a column name as well as data type of the column, as I said we cannot do it in one statement but we first need to change the column name and then data type so let's see this:

Suppose we want to change the column "BlogTypeID" to "Tags" from data type int to varchar(200) in Blogs table then

-- change column name BlogTypeID to Tags    
EXEC sp_rename 'blogs.BlogTypeID', 'Tags', 'COLUMN'    

Once we will run this query we will get a warning "Caution: Changing any part of an object name could break scripts and stored procedures. It indicates that our existing procedures may break but our column name is changes, check the table.

Now we will alter the column data type

ALTER TABLE Blogs
ALTER COLUMN Tags VARCHAR(200)

Now our task is completed to change column and column data type.

How to add a new column in a table

ALTER TABLE table_name
ADD column_name datatype

To change the datatype to support 100 characters and make NOT NULL

ALTER TABLE table_name
ALTER COLUMN column_name VARCHAR(100) NOT NULL

To change datatype and allow NULLs for column

ALTER TABLE table_name
ALTER COLUMN column_name DATETIME NULL

To drop a column from a table

ALTER TABLE table_name
DROP COLUMN column_name   
Hamden Process manager with a reputed organization, Fond of learning new features and technology related to C#, ASP.Net, SQL Server, MVC etc.I like to help others, if I can
  • sql server
By Hamden On 05 May, 13  Viewed: 913

Other blogs you may like

Adding identity to an existing column in SQL Server

We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi   On 02 May 2013  Viewed: 425

some useful sql tricks

There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi   On 31 Mar 2013  Viewed: 541

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 1,368

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 367

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 5,526