--1.增加列
alter table tableName add columnName varchar(30)

--2.修改列类型
alter table tableName alter column columnName varchar(4000)

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

--4.删除列
alter table tableName drop column columnName

--5.添加主键
alter table tableName
add constraint primaryKey_id primary key clustered(id)

--6.级联更新(改主表,外表也改)
UserName varchar(20) foreign key references UserBasicInfo(UserName) on update cascade,--外键UserBasicInfo(UserName) 级联更新UserName

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

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

--9.修改表名
EXEC sp_rename '原表名', '新表名' (T-SQL)

--10.修改 主键列 长度
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 --添加约束 ⑥

--11.字符串批量替换
update table set Fields[字段名]=replace(Fields[字段名],'被替换原内容','要替换成的内容') 

--12.删除数据库所有表

--①删除所有约束
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)

--13.行专列
SELECT name+',' FROM sys.objects WHERE 1=1 FOR XML PATH('')

留言

ˆ