Add/Modify/Resize/Change/Delete/Drop/Rename MS SQL Server Table Column using T-SQL

Please visit my new Web Site https://coderstechzone.com

As a software developer or Sql Server Administrator most often we need to change a column datatype or length or rename even delete or drop. So Add/Modify/Resize/Change/Delete/Drop/Rename operations is a very common task in our daily lives. Lets start an example: Let you start inserting data into a table but getting the ERROR: String or binary data would be truncated.
The reason is you are trying to insert data which exceeds any of your existing column length. So in this scenario you have to increase the column size programatically. Another common problem is
Insert Error: Column name or number of supplied values does not match table definition. The problem is you are trying to insert n no of columns data into a table whereas table has n-1 columns. So in this scenario you need to add a column using T-Sql. Ok now i will describe how we can do this.

Focus Area:
1. Add a new column to a table
2. Modify or Resize an existing column
3. Rename an existing column
4. Drop/Delete/Remove an existing column
5. Rename a Table
6. Drop/Delete/Remove an existing Table

Add a new column to a table:
The ALTER TABLE statement include an ADD clause, followed by the new column specification like datatype & length.

ALTER TABLE Articles ADD Notes VARCHAR(MAX)

ALTER TABLE Sales ADD PartnerComission DECIMAL(18,2)
ALTER TABLE LineItem ADD SecStatus INT NOT NULL

Note: If you have a table with data then you can't add a not nullable column. The solution is first add a null column & then update this column data & then change allownull to false. If your table has no data then you can easily do what you want.

Modify or Resize an existing column:
Almost same as adding a new column except ADD will be changed to ALTER COLUMN.
Ok now we will change the allow null constraint of previous partnercomission column to false.

ALTER TABLE Sales ALTER COLUMN PartnerComission DECIMAL(18,2) NOT NULL;

Note: To change allow null property of a column to False all rows must have contains the value
for this column otherwise you will get "ERROR: 'Sales' table- Unable to modify table. Cannot insert the value NULL into column 'TableName', table 'dbo.Sales'; column does not allow nulls. INSERT fails. The statement has been terminated."

Now we want to reduce the column size of Notes column to 1000. So our query should be:

ALTER TABLE Articles ALTER COLUMN Notes VARCHAR(1000);

Note: Keep in mind that if the table Articles contains length of any Notes column data greater than 1000 then you will get the ERROR: String or binary data would be truncated.
The another change is you may want to change the datataype of an existing column. Then must keep in mind that the new datatype must be cmpatible with existing datatype otherwise you may loose data or get the ERROR: Conversion failed when converting the Previous DataType value '... ' to data type New DataType.

Rename an existing column:

EXEC sp_rename
@objname = 'TableName.OldColumnName',
@newname = 'NewColumnName',
@objtype = 'COLUMN'

For more details on sp_rename you can read MSISDN Article.

Drop/Delete/Remove an existing column:
By using the DROP COLUMN clause, one can remove an existing column. This permanently deletes the column and all of its contents or data. For example, the following command drops the Notes column from the Articles table.

ALTER TABLE Articles DROP COLUMN Notes

Rename a Table:
The builtin "sp_rename" procedure can be used to change or rename the name of an existing table. In this case, the existing name and new name must be provided.

EXEC sp_rename 'OldTableName', 'NewTableName'

For more details on sp_rename you can read MSISDN Article.

Drop/Delete/Remove an existing Table:
If you need to permanently delete or remove an existing table then you can use DROP TABLE statement which will permanently delete your table as well as data. Keep in mind that if the table under a relationship then you can not delete master table without deleting the chaild table first. The syntax for delete command is given below:

DROP TABLE TableName