Modify the SQL command Sql-server database table structure with SQL command line modify database _mssql

Source: Internet
Author: User

No nonsense, please see the code detailed below.

Add a varchar column to the table:

ALTER TABLE Distributors ADD COLUMN address varchar (30);

To delete a field from a table:

ALTER TABLE Distributors DROP COLUMN address RESTRICT;

To modify the type of two existing fields in one operation:

ALTER TABLE Distributors
ALTER COLUMN address TYPE varchar (80),
ALTER COLUMN name TYPE varchar (100);

Converts an integer field containing the UNIX timestamp to timestamp with time zone using a using clause:

ALTER TABLE foo
ALTER COLUMN foo_timestamp TYPE timestamp with time zone
USING
Timestamp with time zone ' epoch ' + foo_timestamp * interval ' 1 second ';

To rename an existing field:

ALTER TABLE Distributors RENAME COLUMN address to City;

Change the name of an existing table:

ALTER TABLE Distributors RENAME to suppliers;

Add a Non-empty constraint to a field:

ALTER TABLE distributors ALTER COLUMN Street SET is not NULL;

Removes a Non-empty constraint from a field:

ALTER TABLE distributors ALTER COLUMN Street DROP not NULL;

Add a check constraint to a table:

ALTER TABLE Distributors ADD CONSTRAINT zipchk CHECK (char_length (zipcode) = 5);

Deletes the OMV constraint for a table and all of its child tables:

ALTER TABLE Distributors DROP CONSTRAINT Zipchk;

Add a FOREIGN KEY constraint to the table:

ALTER TABLE Distributors ADD CONSTRAINT DISTFK FOREIGN KEY (address) REFERENCES addresses [address] MATCH full;

Add a (multiple-field) Unique constraint to a table:

ALTER TABLE Distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, ZipCode);

To add an automatically named PRIMARY KEY constraint to a table, note that a table can have only one primary key:

ALTER TABLE Distributors ADD PRIMARY KEY (dist_id);

To move a table to another table space:

ALTER TABLE Distributors SET tablespace fasttablespace;

Ps:sql the command line to modify the database

Add Columns:

ALTER TABLE tablename add columnName varchar (30)

To modify a column type:

ALTER TABLE tablename ALTER COLUMN COLUMNNAME varchar (4000)

To modify the name of a column:

EXEC sp_rename ' tablename.column1 ', ' column2 ' (change the Column1 column name of the table name TableName to Column2)

To delete a column:

ALTER TABLE tablename DROP column ColumnName

The above content is the whole narration of this article, hope to be helpful to everybody.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.