SQL Server common statements used to create tables, add primary keys, and add columns

 

Create a table:
Create Table [Table name]
(
[Automatic number field] int identity (1, 1) primary key,
[Field 1] nvarchar (50) Default \ 'default value \ '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,
)

Delete table:
Drop table [Table name]

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 3] = \ 'haiwa \'

New field:
Alter table [Table name] add [field name] nvarchar (50) null

Delete field:
Alter table [Table name] Drop column [field name]

Modify Field:
Alter table [Table name] alter column [field name] nvarchar (50) null

Rename a table: (access to rename a table, refer to the article: rename a table in Access database)
Sp_rename \ 'table name \ ', \ 'new table name \', \ 'object \'

New constraint:
Alter table [Table name] add constraint name check ([constraint field] <= \ '2014-1-1 \')

Delete constraints:
Alter table [Table name] Drop constraint name

Create Default Value
Alter table [Table name] add constraint default name: Default \ '51windows. Net \ 'for [field name]

Delete default value
Alter table [Table name] Drop constraint default name

Delete logs in SQL Server to reduce the size of database files
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 a common field function
Sub AddColumn (tablename, columnname, columntype)
Conn. Execute (\ "alter table \" & tablename & \ "add \" & columnname & \ "\" & columntype &\"\")

End sub

\\\ 'Change the common function of a field
Sub modcolumn (tablename, columnname, columntype)
Conn. Execute (\ "alter table \" & tablename & \ "alter column \" & columnname & \ "\" & columntype &\"\")

End sub

\\\' Check whether 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 rsw.conn.exe cute (SQL)

Response. Write RS (\ "Dida \") \ 'returns a value. 0 indicates none, and 1 indicates existence.

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