Free Web Development Online Tutorials, Learn to Code
How to Alter a Table in SQL, Learn SQL Database Development, MS SQL Tutorial Online

Alter Table Syntax in SQL

Altering a table in SQL, means, making changes in table structure, like changing a column name, adding a new column, deleting a column or adding a constant etc.

SQL Alter Table

Here you learn alter table statement in sql server, and how to alter column in sql table.

(Note: In this tutorial i am not talking about updating data in SQL table, talking about altering / updating SQL Table structure). The SQL Server (Transact-SQL) ALTER TABLE statement is used to add, modify or drop columns in a table.

How to add a new column in (Alter) Existing SQL Table

Alter table statement in sql server, In case you want to add a new column in your existing SQL Table.

Alter  TABLE [tableName]            
Add  ColumnName nvarchar(1000)

How to change a column name in (Alter) Existing SQL Table

Alter TABLE, Change column name in SQL Table, To change the name of the column in some particular table we will be built-in SQL store procedure “sp_rename” , here is an example.

sp_rename '[tableName].ColumnName', 'NewColumnName', 'COLUMN';

Note: After changing Column name in SQL Table may break your scripts and stored procedures, views, function, triggers. So, Remember to change them in all other places

How to drop a column in (Alter) Existing SQL Table
In case you want to drop a column from SQL table permanently

ALTER TABLE [tableName]            
DROP COLUMN ColumnName;

Once you drop the column all data in that particular column will be lost permanently

How to rename a Table in SQL Server
Remember, You can not use the ALTER TABLE statement in SQL Server to Rename a SQL Table, there is a system stored procedure called "sp_rename" to do that job.

Here is the syntax to Rename a table in SQL Server.
 sp_rename 'old_table_name', 'new_table_name';

Note: even after changing the table name, all constraint names like primary key, foreign key will remain the same

Comment
Name
Email
Website
Subscribe
MS SQL Examples | Join MS SQL Course