增加列

alter table tableName add columnName varchar(30)

修改列类型

alter table tableName alter column columnName varchar(4000)

修改列的名称

EXEC  sp_rename  'tableName.column1' , 'column2'  --(把表名为tableName的column1列名修改为column2)

删除列

alter table tableName drop column columnName

添加主键

alter table tableName
add constraint primaryKey_id primary key clustered(id)

级联更新(改主表，外表也改)

UserName varchar(20) foreign key references UserBasicInfo(UserName) on update cascade,
--外键UserBasicInfo(UserName) 级联更新UserName

级联删除(删主表，外表也删)

UserName varchar(20) foreign key references UserBasicInfo(UserName) on delete cascade,
--外键UserBasicInfo(UserName) 级联删除UserName

插入增长列指定ID

set identity_insert tableName ON --允许对自增列Id插入指定数据
insert into tableName(id,GroupName) values(1,'（未分配）')
set identity_insert tableName OFF --关闭对自增列Id插入指定数据

修改表名

EXEC sp_rename '原表名', '新表名' (T-SQL)

修改 主键列 长度

alter table UserInfo drop constraint PK__UserInfo__C9 --删除主键 ③
alter table UserInfo alter column UserName varchar(50) not null --修改列 ④
alter table UserInfo add constraint PK__UserInfo__C9 primary key(UserName) --添加主键 ⑤

ALTER TABLE BBSInfoH DROP CONSTRAINT FK__BBSInfoL__UserNa__37 --删除约束 ①
alter table BBSInfoH alter column UserName varchar(50) not null --修改列 ②
alter TABLE BBSInfoH add constraint FK__BBSInfoL__UserNa__37 foreign key(UserName) references UserInfo(UserName) on update cascade on delete cascade --添加约束 ⑥

字符串批量替换

update table set Fields[字段名]=replace(Fields[字段名],'被替换原内容','要替换成的内容') 

删除数据库所有表

--①删除所有约束
DECLARE c1 cursor for 
    select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
    from sysobjects 
    where xtype = 'F'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
    begin 
        exec(@c1)
        fetch next from c1 into @c1
    end
close c1
deallocate c1 

--②删除所有表
declare @sql varchar(8000)
while (select count(*) from sysobjects where type='U')>0
begin
SELECT @sql='drop table ' + name
FROM sysobjects
WHERE (type = 'U')
ORDER BY 'drop table ' + name
exec(@sql) 
end

--③删除所有存储过程
declare @tname varchar(8000)
set @tname=''
select @tname=@tname + Name + ',' from sysobjects where xtype='P'
select @tname='drop Procedure ' + left(@tname,len(@tname)-1)
exec(@tname)

行转列

SELECT name+',' FROM sys.objects WHERE 1=1 FOR XML PATH('')