alter table tablename drop constraint 约束名 drop table tablename
不能够用sql删除表的关系吗?
是啊,我的问题就是怎么用SQL来删除表的关系啊?
alter table 表名 drop constraint 約束名
在对象浏览器里找到列名 然后查看约束 如果不想用语法删可以直接右键删除默认约束 如果使用语法 使用 sp_helpconstraint 表 可以查看表约束名alter table 表 drop constraint 约束名称 alter table 表 drop column 字段
夷,怎么我好像直接 drop table 会把他的外健约束给删掉呢?其他的我没试过...
--drop table a create table a ( id int not null , name varchar(10) ) --drop table bcreate table b ( id int not null , name varchar(10) )alter table b add constraint pk_a_id PRIMARY KEY (id ) alter table a add constraint pk_b_id PRIMARY KEY (id )alter table b add constraint fk_b_id Foreign KEY (id ) REFERENCES a(id)select *from b insert into a select 1,'Jack' union all select 2 ,'FK'insert into b select 1,'a'if exists ( select object_Id('fk_b_id') ) print 1 else print 0drop table b if exists ( select object_Id('fk_b_id') ) print 1 else print 0
ALTER TABLE Table2 ADD CONSTRAINT Relation1 FOREIGN KEY ([Id]) _ REFERENCES Table1 ([Id]) ALTER TABLE Table2 DROP CONSTRAINT Relation1 DROP TABLE Table2
-- 禁用所有约束 EXEC sp_msforeachtable @command1=N'ALTER TABLE ? NOCHECK CONSTRAINT ALL' -- 删除有数据R的表 EXEC sp_msforeachtable @command1=N'DELETE ?', @whereand=N'AND EXISTS( SELECT rows FROM dbo.sysindexes WHERE id=O.id AND rows>0 AND indid<2)' -- 启用所有约束 EXEC sp_msforeachtable @command1=N'ALTER TABLE ? CHECK CONSTRAINT ALL' GO
存过太多就不写了显示一个表的所有约束 select name from sysobjects where parent_obj=(select [id] from sysobjects where [name]=你要查的表名)如果只显示外建 select name from sysobjects where parent_obj=(select [id] from sysobjects where [name]=你要查的表名) and xtype='D'
字母写错 重发: 存过太多就不写了显示一个表的所有约束 select name from sysobjects where parent_obj=(select [id] from sysobjects where [name]=你要查的表名)如果只显示外建 select name from sysobjects where parent_obj=(select [id] from sysobjects where [name]=你要查的表名) and xtype='F'
显示约束只能够显示名称啊,怎么转换成对象呢?比如下面的语句: alter table base_userlog drop constraint (select name from sysobjects where parent_obj= (select [id] from sysobjects where [name]='table' and xtype = 'u') and xtype = 'f') 即要实现查出一个表所有的外键,并且删除这些外键,不知道该怎么写? 用sp_msforeachtable 又该怎么写? 主要是没有玩过这种的,有个demo就好了
怎么玩?就是要实现上面我写的: alter table base_userlog drop constraint (select name from sysobjects where parent_obj= (select [id] from sysobjects where [name]='table' and xtype = 'u') and xtype = 'f') ------------------------- 上面的写法是错误的,因为name是字符串,而drop constraint 后面跟的是对象
alter table tablename drop constraint constraintname drop table
算了,自己写了个存过搞定,需要的人可以看看: alter proc DeleteSingleTable(@tablename varchar(100)) as begin declare @SQL varchar(2000) declare @constraintName varchar(100) declare curName cursor for select name from sysobjects where xtype = 'f' and parent_obj = (select [id] from sysobjects where [name]=@tablename and xtype = 'u')
open curName fetch next from curName into @constraintName while @@fetch_status = 0 begin set @SQL = 'alter table ' + @tablename + ' drop constraint ' set @SQL = @SQL + @constraintName exec(@SQL) fetch next from curName into @constraintName end close curName deallocate curName end ----- 结贴。但是sp_msforeachtable 的用法不怎么懂,遗憾....
有个casecade好像可以连带删除
drop table tablename
drop constraint 約束名
如果使用语法
使用 sp_helpconstraint 表 可以查看表约束名alter table 表 drop constraint 约束名称
alter table 表 drop column 字段
create table a
(
id int not null ,
name varchar(10)
)
--drop table bcreate table b
(
id int not null ,
name varchar(10)
)alter table b add constraint pk_a_id PRIMARY KEY (id )
alter table a add constraint pk_b_id PRIMARY KEY (id )alter table b add constraint fk_b_id Foreign KEY (id ) REFERENCES a(id)select *from b insert into a select 1,'Jack'
union all select 2 ,'FK'insert into b
select 1,'a'if exists ( select object_Id('fk_b_id') )
print 1
else
print 0drop table b if exists ( select object_Id('fk_b_id') )
print 1
else
print 0
如果有其他表的外键引用被删除表的主键,只能先删掉外键关系,再drop。
具体sql语句记不清了,可以在sql server环境下导出sql看看
REFERENCES Table1 ([Id])
ALTER TABLE Table2 DROP CONSTRAINT Relation1
DROP TABLE Table2
EXEC sp_msforeachtable
@command1=N'ALTER TABLE ? NOCHECK CONSTRAINT ALL' -- 删除有数据R的表
EXEC sp_msforeachtable
@command1=N'DELETE ?',
@whereand=N'AND EXISTS(
SELECT rows FROM dbo.sysindexes
WHERE id=O.id
AND rows>0
AND indid<2)' -- 启用所有约束
EXEC sp_msforeachtable
@command1=N'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
select name from sysobjects where parent_obj=(select [id] from sysobjects where [name]=你要查的表名)如果只显示外建
select name from sysobjects where parent_obj=(select [id] from sysobjects where [name]=你要查的表名)
and xtype='D'
重发:
存过太多就不写了显示一个表的所有约束
select name from sysobjects where parent_obj=(select [id] from sysobjects where [name]=你要查的表名)如果只显示外建
select name from sysobjects where parent_obj=(select [id] from sysobjects where [name]=你要查的表名)
and xtype='F'
alter table base_userlog
drop constraint
(select name from sysobjects
where parent_obj=
(select [id] from sysobjects where [name]='table' and xtype = 'u')
and xtype = 'f')
即要实现查出一个表所有的外键,并且删除这些外键,不知道该怎么写?
用sp_msforeachtable 又该怎么写?
主要是没有玩过这种的,有个demo就好了
alter table base_userlog
drop constraint
(select name from sysobjects
where parent_obj=
(select [id] from sysobjects where [name]='table' and xtype = 'u')
and xtype = 'f')
-------------------------
上面的写法是错误的,因为name是字符串,而drop constraint 后面跟的是对象
drop table
alter proc DeleteSingleTable(@tablename varchar(100))
as
begin
declare @SQL varchar(2000)
declare @constraintName varchar(100) declare curName cursor for
select name from sysobjects
where xtype = 'f' and parent_obj =
(select [id] from sysobjects where [name]=@tablename and xtype = 'u')
open curName
fetch next from curName into @constraintName
while @@fetch_status = 0
begin
set @SQL = 'alter table ' + @tablename + ' drop constraint '
set @SQL = @SQL + @constraintName
exec(@SQL)
fetch next from curName into @constraintName
end
close curName
deallocate curName
end
-----
结贴。但是sp_msforeachtable 的用法不怎么懂,遗憾....