Home / Database / SQL Server / How to Modify table information in SQL database?
Modify Table

How to Modify table information in SQL database?

Modify Table Structure:

We can modify table structure by adding/altering the table using following commands.

Add Columns:

  • While adding columns constraints can be applied to the columns.
  • If tables contains data then it won’t be possible to apply “Not Null” & “Primary Key” constraint on the column.
  • Syntax is :

Alter Table < Table Name >

Add < Column Name > < Datatype > [Constraints], ……………

Add Constraint:

  • Syntax is:

Alter Table < Table Name > [With Check/No Check]

Add [Constraint < Constraint Name >], ……………

  • The column on which constraint will be applied, contains the data which does not satisfies the constraint condition then constraint will not be added on that column.
    Because while adding constraint on the column the existing data will be validated against the constraint condition before adding the constraint on that column.
  • We can add constraint without checking the existing data of the table by using “No Check” option.

Alter Column:

  • Using “Alter Column” command the datatype of column can be changed.
  • We can add/remove “Not Null” constraint on the column.
  • We can increase or decrease the size of the column.
  • Syntax is:

Alter Table < Table Name >

Alter Column < Column Name > < Datatype > [ Null / Not Null ]

Drop Column:

  • Using this command the column can be dropped.
  • Deleting a “Primary Key” column is not possible if it is referred by foreign key.
  • Syntax is :

Alter Table < Table Name >

Drop Column < Column Name > ,< Column Name > ,………

Drop Constraint:

  • Using this command the Constraint can be dropped.
  • Deleting a “Primary Key” constraint is not possible if it is referred by foreign key.
  • Syntax is :

Alter Table < Table Name >

Drop Constraint < Constraint Name >

Table Rename:

  • Sp_rename ‘< Old Name of Table >’, ‘< New Name for Table >’

Column Rename:

  • Sp_rename ‘ < Table Name > . < Column Name >’, ‘< New Name>’

About Helpservices

About This site (allhelpservices.com) is dedicated to helping other bloggers learn the skills of blogging, wordpress, education, technology, social media sharing , SEO, Electronics, Earn Money From Blogging, Web hosting and web share their own experiences and promote the blogging medium. We started Allhelpservices in August 2015 mainly because we wanted to keep a record of what we wear learning from development and because we wanted to connect with other learners.

Check Also

Database

Database level Queries & Identity functions

Get list of tables in the Database: We can get all the tables in the ...

Leave a Reply