CREATE TABLE Add primary key Add column common SQL statement

--Delete primary key

ALTER TABLE name drop constraint primary Key name
--Add primary key
ALTER TABLE name add constraint primary Key name primary key (field name 1, field Name 2 ...)
--Add a primary key for a nonclustered index
ALTER TABLE name add constraint primary Key name primary key nonclustered (field name 1, field Name 2 ...)

New table:
CREATE table [table name]
(
[AutoNumber field] int IDENTITY (PRIMARY KEY),
[Field 1] NVarChar (+) default \ ' defaults \ ' null,
[Field 2] ntext null,
[Field 3] datetime,
[Field 4] money null,
[Field 5] int default 0,
[Field 6] Decimal (12,4) default 0,
[Field 7], image null,
)

To delete a table:
Drop table [table name]

Delete all tables:

DECLARE Curitems CURSOR
For select [name] from sysobjects where xtype= ' U '
For READ only
OPEN Curitems
DECLARE @n NVARCHAR (+), @m NVARCHAR (100)
FETCH from Curitems to @n
While @ @FETCH_STATUS =0
BEGIN
Set @[email protected]
EXEC (' Drop Table ' + @m)
FETCH NEXT from Curitems to
@n
END
CLOSE Curitems
Deallocate Curitems

Insert data:
INSERT into [table name] (field 1, Field 2) VALUES (100,\ ' 51windows.net\ ')

Delete data:
DELETE from [table name] WHERE [field name]>100

Update data:
UPDATE [table name] SET [Field 1] = 200,[Field 2] = \ ' 51windows.net\ ' WHERE [field three] = \ ' Haiwa\ '

New fields:
ALTER table [table name] ADD [field name] NVARCHAR () NULL

To delete a field:
ALTER table [table name] DROP COLUMN [field name]

To modify a field:
ALTER TABLE [table name] Alter COLUMN [field name] NVARCHAR () NULL

Rename table: (Access rename table, refer to article: renaming tables in an Access database)
sp_rename \ ' table name \ ', \ ' new table name \ ', \ ' object\ '

New constraint:
ALTER table [table name] ADD CONSTRAINT constraint name CHECK ([constraint field] <= \ ' 2000-1-1\ ')

To delete a constraint:
ALTER table [table name] DROP CONSTRAINT constraint name

New default value
ALTER table [table name] ADD CONSTRAINT default name \ ' 51windows.net\ ' for [field name]

Delete default values
ALTER table [table name] DROP CONSTRAINT Default value name

Reduce the size of the database file by removing logs from SQL Server
Dump TRANSACTION database name with NO_LOG
Backup log database name with NO_LOG
DBCC SHRINKDATABASE (database name)
exec sp_dboption \ ' database name \ ', \ ' autoshrink\ ', \ ' true\ '

\\\ ' Add field general function
Sub AddColumn (Tablename,columnname,columntype)
Conn.execute (\ "Alter Table \" &tablename&\ "ADD \" &columnname&\ "\" &columntype&\ "\")
End Sub

\\\ ' Change field general function
Sub Modcolumn (Tablename,columnname,columntype)
Conn.execute (\ "Alter Table \" &tablename&\ "alter Column \" &columnname&\ "\" &columntype&\ "\")
End Sub

\\\ ' Check if the table exists

Sql=\ "SELECT COUNT (*) as Dida from sysobjects where id = object_id (n\ ' [owner].[ Table name]\ ') and OBJECTPROPERTY (ID, n\ ' isusertable\ ') = 1\ "

Set Rs=conn.execute (SQL)

Response.Write rs (\ "dida\") \ ' Returns a numeric value, 0 means no, 1 represents existence

To determine the existence of a table:
SELECT * from sysobjects where id = object_id (n\ ' [dbo].[ Tablename]\ ') and OBJECTPROPERTY (ID, n\ ' isusertable\ ') = 1

Structure of a table
SELECT * from syscolumns where id = object_id (n\ ' [dbo].[ Your table name]\ ') and OBJECTPROPERTY (ID, n\ ' isusertable\ ') = 1

To modify the table prefix:

ALTER SCHEMA dbo TRANSFER prename.tablename;

If table 2 already exists, add the records in table 1 to the statement in Table 2:
Insert INTO Table 2 (Field 1, Field 2,...)   Select field 1, Field 2,.. From table 2 where ...

If Table 2 does not exist, table 2 is automatically generated with the following statement, with the same type of field as Table 1:
Select field 1, Field 2,.. into table 2 from table 1 where ...

Reprinted from: http://blog.csdn.net/xiaoxiong1212/article/details/7333595

CREATE TABLE Add primary key Add column common SQL statement