Alter Table SQL Server

 

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

---

ALTER TABLE - ADD Column

To add a column in a table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype;

The following SQL adds an "Email" column to the "Customers" table:

Example

ALTER TABLE Customers
ADD Email varchar(255);

 

---

ALTER TABLE - DROP COLUMN

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

ALTER TABLE table_name
DROP COLUMN column_name;

The following SQL deletes the "Email" column from the "Customers" table:

Example

ALTER TABLE Customers
DROP COLUMN Email;

---

ALTER TABLE - ALTER/MODIFY COLUMN

To change the data type of a column in a table, use the following syntax:

SQL Server / MS Access:

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

Rename a column

SQL Server changes column using T-SQL statement using sp_rename function. Syntax and example given below

Syntax

sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';

 Example 

sp_rename 'career.Phone', 'Phone1', 'COLUMN';

Rename A Table

Syntax

EXEC sp_rename 'old_table_name', 'new_table_name';

 Example 

EXEC sp_rename 'designationmaster', 'UserTypeMaster';

Show Table Structure

EXEC sp_help 'dbo.mytable';

 Reset Identity value to A fixed number 

Reset Identity value to A fixed number for adjust gap due to server restart

It now by default uses a cache size of 1,000 when allocating IDENTITY values for an int column and restarting the service can "lose" unused values (The cache size is 10,000 for bigint/numeric).

DBCC CHECKIDENT ('[databasename].dbo.[table_name]', reseed, <number to set indentity field>)

DBCC CHECKIDENT ('[girfa_85inch].dbo.[CityMaster]', reseed, 5)

 

Next Query