--Remove primary key
alter table table name drop constraint primary key name
--Add primary key
alter table table add constraint primary key (field name 1, field name 2......)
--Add primary key for non-clustered indexes
alter table table name add constraint primary key NONCLUSTERED(field name 1, field name 2......)
New table:
create table [table name]
(
[Auto-numbered field] int IDENTITY (1,1) PRIMARY KEY ,
[Field 1] nVarChar(50) default \'default\' 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]
Delete all tables:
DECLARE curItems CURSOR
FOR select [name] from sysobjects where xtype='U'
FOR READ ONLY
OPEN curItems
DECLARE @n NVARCHAR(100),@m NVARCHAR(100)
FETCH FROM curItems INTO @n
WHILE @@FETCH_STATUS=0
BEGIN
set @m=@n
exec('Drop Table ' + @m)
FETCH NEXT FROM curItems INTO
@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
Updated 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 (50) NULL
Delete a field:
ALTER TABLE [Table Name] DROP COLUMN [Field Name]
Modify the field:
ALTER TABLE [table name] ALTER COLUMN [field name] NVARCHAR (50) NULL
Rename a table: (For access to rename a table, please refer to the article: Renaming a table in an Access database)
sp_rename \'Table Name\', \'New Table Name\', \'OBJECT\'
New constraints:
ALTER TABLE [table name] ADD CONSTRAINT CHECK ([constraint field] <= \'2000-1-1\')
Remove constraints:
ALTER TABLE [table name] DROP CONSTRAINT CONSTRAINT name
Create a new default
ALTER TABLE [table name] ADD CONSTRAINT default value name DEFAULT \'51WINDOWS.NET\' FOR [field name]
Delete the default value
ALTER TABLE [table name] DROP CONSTRAINT default value name
Delete logs in Sql Server to reduce database file size
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 generic function
Sub AddColumn(TableName,ColumnName,ColumnType)
Conn.Execute(\"Alter Table \"&TableName&\" Add \"&ColumnName&\" \"&ColumnType&\"\")
End Sub
\\\'Change the field generic function
Sub ModColumn(TableName,ColumnName,ColumnType)
Conn.Execute(\"Alter Table \"&TableName&\" Alter Column \"&ColumnName&\" \"&ColumnType&\"\")
End Sub
\\\' checks 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 for no, and 1 for existence
The existence of a judgment 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
Modify the prefix of the table:
ALTER SCHEMA dbo TRANSFER prename.tablename;
If Table 2 already exists, add the records from 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 will be automatically generated with the following statement, and the field type is the same as Table 1:
select Field 1, Field 2,.. INTO Table 2 from Table 1 where ...
|
Posted on 5/15/2015 9:10:13 PM
|
|
|
