CREATE TABLE Add primary key Add column common SQL statement

Source: Internet
Author: User
Tags access database

--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

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.