如果表t中有两列a,b (两列数据类型要兼容)
update t set a = b,b = a
查两个表中不一样的数据
select * from a
except
select * from b
外键
---启用or禁用指定表所有外键约束
alter
table
PUB_STRU NOCHECK
constraint
all
;
alter
table
PUB_STRU
CHECK
constraint
all
;
---生成启用or禁用指定表外键约束的sql
select
'ALTER TABLE '
+ b.
name
+
' NOCHECK CONSTRAINT '
+ a.
name
+
';'
from
sysobjects a ,sysobjects b
where
a.xtype =
'f'
and
a.parent_obj = b.id
and
b.
name
=
'表名'
;
select
'ALTER TABLE '
+ b.
name
+
' CHECK CONSTRAINT '
+ a.
name
+
';'
from
sysobjects a ,sysobjects b
where
a.xtype =
'f'
and
a.parent_obj = b.id
and
b.
name
=
'表名'
;
--生成的sql如下
ALTER
TABLE
PUB_STRU NOCHECK
CONSTRAINT
PUBSTRU_FK1;
ALTER
TABLE
PUB_STRU NOCHECK
CONSTRAINT
PUBSTRU_FK2;
ALTER
TABLE
PUB_STRU
CHECK
CONSTRAINT
PUBSTRU_FK1;
ALTER
TABLE
PUB_STRU
CHECK
CONSTRAINT
PUBSTRU_FK2;
--查看约束状态(查询字典表 sys.foreign_keys,该字典表开始出现于sqlserver2005及以上版本):
select
name
, is_disabled
from
sys.foreign_keys
order
by
name
;
--其中:name : 外键约束名称 is_disabled : 是否已禁用
--删除外键
alter
table
AdItem
drop
constraint
AdOrder_AdItem_FK1
--增加外键
alter
table
AdItem
add
constraint
AdOrder_AdItem_FK1
foreign
key
(AI_nOrderNo)
references
AdOrder(AO_nOrderNo)
--单个表的一个外键
alter
table
Student nocheck
constraint
FK__Student__SchoolN__4222D4EF
alter
table
Student
check
constraint
FK__Student__SchoolN__4222D4EF
--单个表的所有外键
alter
table
Student nocheck
constraint
all
alter
table
Student
check
constraint
all
--某个数据库的所有表
EXEC
sp_MSforeachtable @command1=
'alter table ? NOCHECK constraint all;
EXEC sp_MSforeachtable @command1='
alter
table
?
CHECK
constraint
all
;