SqlServer常用语句整理

初始化表
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