Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Query to Alter,Add,modify,delete columns in table - sql server

Jun 5, 2012
Introduction:

In this article I will explain how to write SQL Query to add new column to existing table in sql server and query to delete column from table in sql and query to modify column in existing table.

Description:

In previous posts I explained many articles relating to SQL Server. Now I will explain how to write query to add, remove or modify column in existing table using SQL Server. During work with one application I got requirement like add new column to existing table in SQL Server at that time I realized that it’s better to write query to add new column instead of open table structure and modify to add new column in SQL Server. To add new column to existing table in SQL server the declaration of system will be like this

Query to add new column to table

If we want to add new column to existing table that syntax will be like this


ALTER TABLE Table_Name ADD COLUMN_NAME DATATYPE
Ex:


ALTER TABLE emp_table ADD Manager_Name VARCHAR(50)
From the above query declaration new column “Manager_Name” will add to emp_table with data type VARCHAR(50)

Query to Drop or delete column from existing table

If we want to drop column from existing table that syntax will be like this


ALTER TABLE Table_Name DROP COLUMN COLUMN_NAME
Ex:


ALTER TABLE emp_table DROP COLUMN Manager_Name
From the above query declaration column “Manager_Name” will drop from emp_table

Query to modify existing column datatype in table

If we want to modify existing column datatype from data table that syntax will be like this


ALTER TABLE Table_Name ALTER COLUMN COLUMN_NAME DATATYPE
Ex:


ALTER TABLE emp_table ALTER COLUMN Manager_Name nvarchar(max)
From the above query declaration I changed “Manager_Name” column datatype from VARCHAR(50) to nvarchar(max)

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

7 comments :

SAVIOUR said...

hello , i have a doubt , see my requirement is to add a new column to an existing table such tat the column name should be automatically given the name of the variable . i wrote the query as
alter table test add'+@count+' int. , but i am getting error any idea how to solve this , waiting for your reply

srinivas said...

please write a post on javascript events in my system have firefox 13.0 version it is not firing on key press event i want to validate against numarics

srinivas said...

iam using 3rd party controls dbnetsuite please can you help as it is urgent requirement for me please suresh..................?

srinivas said...

please write a post on javascript events in my system have firefox 13.0 version it is not firing on key press event i want to validate against numarics

Anonymous said...

hi

Anonymous said...

nice

Sumit Jaiswal said...

alter table test add'+@count+' int

exec('alter table test add ['+@count+'] int')

Give your Valuable Comments

Other Related Posts

© 2010-2012 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.