Common SQL statements for access and SQL Server operations database structure

access|server|sql| Data | database | database STRUCTURE | statement

The following are common SQL Server and Access operations database structures that you want to help.
The content is sorted by Eva, the incorrect and incomplete place also please put forward, thank you.

New table:
CREATE table [table name]
(
AutoNumber field int IDENTITY (1,1) 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]

Insert data:
Insert into [table name] (field 1, Field 2) VALUES (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 (m) 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 (m) NULL

Renaming tables: (Access renames tables, 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 ')

Delete constraint:
Alter table [table name] Drop CONSTRAINT constraint name

Create a new default value
Alter table [table name] ADD CONSTRAINT Default value name ' 51WINDOWS '. NET ' for [field name]

Delete default values
Alter table [table name] Drop CONSTRAINT Default value name

Delete logs in SQL Server to reduce the size of the database file
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 field common 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 Rs=conn.execute (SQL)

Response.Write rs ("Dida") ' Returns a numeric value, 0 represents 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

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