SQL Server tables, views, indexes (create, modify, delete) related examples

First, the table related

1. Create

USE [TEST]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [DBO] . [Ceshi] ([Id] [int] not null,[name] [varchar ()] null, constraint [pk_ceshi]  primary key clustered  ([Id] asc) with  (Pad_index = off, statistics_ norecompute = off, ignore_dup_key = off, allow_row_locks = on,  Allow_page_locks = on)  on [primary])  on [primary]go--add field comment exec sys.sp_ addextendedproperty  @name =n ' ms_description ',  @value =n ' primary key One '  ,  @level0type =n ' SCHEMA ', @ Level0name=n ' dbo ',  @level1type =n ' TABLE ', @level1name =n ' Ceshi ',  @level2type =n ' COLUMN ', @level2name =n ' ID ' go--Modify field comment exec sys.sp_updateextendedproperty  @name =n ' ms_description ',  @value =n ' primary key One '     @level0type =n ' SCHEMA ', @level0name =n ' dbo ',  @level1type =n ' TABLE ', @level1name =n ' Ceshi ',  @ Level2type=n ' COLUMN ', @level2Name=n ' id ' GO 

2. Modification

(1) Modify the table name:

EXEC sp_rename ' table_name ', ' table_new_name '

(2) New fields:

ALTER TABLE table_name ADD column_name datatype

(3) Modify field name:

EXEC sp_rename ' table name. column_name ', ' new_column_name ', ' column '

(4) Modify the field type:

ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_NAME datatype

(5) Delete a field:

ALTER TABLE table_name DROP COLUMN column_name

3. Delete

DROP TABLE ' test ';

Second, view-related

1. Create

Use [Test]goset ansi_nulls ongoset quoted_identifier ongo--comment unload here create VIEW view_name as SELECT column_name (s) from TA Ble_name WHERE Conditiongo

2. Modification

ALTER VIEW view_name as SELECT * from Ceshi;

3. Delete

DROP VIEW view_name

Third, index-related

1. Create

(1) Create a simple index on the table

Use [test]gocreate nonclustered INDEX index_name on table_name (column_name asc,column_name2 DESC) with (Pad_index = OFF, ST Atistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, ONLINE = off, Allow_row_locks = ON, Allow_page_lock S = ON) on [Primary]go

(2) Create a unique index on the table

Use [test]gocreate CLUSTERED INDEX index_name on table_name (column_name ASC) with (Pad_index = OFF, Statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, ONLINE = off, Allow_row_locks = on, allow_page_locks = on) on [PRIMARY] GO

2. Delete

Use [Test]godrop INDEX index_name on table_name with (ONLINE = OFF) GO

This article is from the "Wind Trace _ Snow Tiger" blog, please be sure to keep this source http://snowtiger.blog.51cto.com/12931578/1929104

SQL Server tables, views, indexes (create, modify, delete) related examples