初始化表
TRUNCATE TABLE 表名
如果想要数据保留可以重置自增列(如果有)
DBCC CHECKIDENT (tablename,reseed,0)
根据TabelA 更新TabelB
//方法一
update t1 set t1 .Name= t2 .Name from TabelA as t1,TabelB as t2 where t1.Code= t2 .Code
//方法二
update TabelA set CDate=(select MAX(CDate) from TabelB where TabelA.Code = TabelB.Code)
–删除主键
alter table 表名 drop constraint 主键名
–添加主键
alter table 表名 add constraint 主键名 primary key(字段名1,字段名2……)
–添加非聚集索引的主键
alter table 表名 add constraint 主键名 primary key NONCLUSTERED(字段名1,字段名2……)
–新建表:
create table [表名] ( [自动编号字段] int IDENTITY (1,1) PRIMARY KEY , [字段1] nVarChar(50) default \'默认值\' null , [字段2] ntext null , [字段3] datetime, [字段4] money null , [字段5] int default 0, [字段6] Decimal (12,4) default 0, [字段7] image null , )
–删除表:
Drop table [表名]
–删除所有表:
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 INTO [表名] (字段1,字段2) VALUES (100,\'51WINDOWS.NET\')
–删除数据:
DELETE FROM [表名] WHERE [字段名]>100
–更新数据:
UPDATE [表名] SET [字段1] = 200,[字段2] = \'51WINDOWS.Net\' WHERE [字段三] = \'HAIWA\'
–新增字段:
ALTER TABLE [表名] ADD [字段名] NVARCHAR (50) NULL
–删除字段:
ALTER TABLE [表名] DROP COLUMN [字段名]
–修改字段:
ALTER TABLE [表名] ALTER COLUMN [字段名] NVARCHAR (50) NULL
–重命名表:(Access 重命名表,请参考文章:在Access数据库中重命名表)
sp_rename \'表名\', \'新表名\', \'OBJECT\'
–新建约束:
ALTER TABLE [表名] ADD CONSTRAINT 约束名 CHECK ([约束字段] <= \'2000-1-1\')
–删除约束:
ALTER TABLE [表名] DROP CONSTRAINT 约束名
–新建默认值
ALTER TABLE [表名] ADD CONSTRAINT 默认值名 DEFAULT \'51WINDOWS.NET\' FOR [字段名]
–删除默认值
ALTER TABLE [表名] DROP CONSTRAINT 默认值名
–删除Sql Server 中的日志,减小数据库文件大小
dump transaction 数据库名 with no_log backup log 数据库名 with no_log dbcc shrinkdatabase(数据库名) exec sp_dboption \'数据库名\', \'autoshrink\', \'true\'
–\'添加字段通用函数
Sub AddColumn(TableName,ColumnName,ColumnType) Conn.Execute(\"Alter Table \"&TableName&\" Add \"&ColumnName&\" \"&ColumnType&\"\") End Sub
–'更改字段通用函数
Sub ModColumn(TableName,ColumnName,ColumnType) Conn.Execute(\"Alter Table \"&TableName&\" Alter Column \"&ColumnName&\" \"&ColumnType&\"\") End Sub
–'检查表是否存在
sql=\"select count(*) as dida from sysobjects where id = object_id(N\'[所有者].[表名]\') and OBJECTPROPERTY(id, N\'IsUserTable\') = 1\" set rs=conn.execute(sql) response.write rs(\"dida\")
–'返回一个数值,0代表没有,1代表存在
–判断表的存在:
select * from sysobjects where id = object_id(N\'[dbo].[tablename]\') and OBJECTPROPERTY(id, N\'IsUserTable\') = 1
–某个表的结构
select * from syscolumns where id = object_id(N\'[dbo].[你的表名]\') and OBJECTPROPERTY(id, N\'IsUserTable\') = 1
–修改表的前缀:
ALTER SCHEMA dbo TRANSFER prename.tablename;
–如果表2已经存在,把表1中的记录加到表2中的语句:
insert into 表2 (字段1,字段2,...) select 字段1,字段2,.. from 表2 where ...
–如果表2不存在,则用下面的语句会自动生成表2,字段的类型和表1一样:
select 字段1,字段2,.. INTO 表2 from 表1 where ...
查看数据库表大小
if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tabName') and xtype='u')
drop table #tabName
go
create table #tabName(
tabname varchar(100),
rowsNum varchar(100),
reserved varchar(100),
data varchar(100),
index_size varchar(100),
unused_size varchar(100)
)
declare @name varchar(100)
declare cur cursor for
select name from sysobjects where xtype='u' order by name
open cur
fetch next from cur into @name
while @@fetch_status=0
begin
insert into #tabName
exec sp_spaceused @name
--print @name
fetch next from cur into @name
end
close cur
deallocate cur
-- 已经经过优化
select tabname as '表名',rowsNum as '表数据行数',reserved as '保留大小',convert(int,SUBSTRING(data,0,LEN(data)-2)) size, data as '数据大小',index_size as '索引大小',unused_size as '未使用大小'
from #tabName ORDER BY size desc