gpt4 book ai didi

SQL Server 删除表

转载 作者:行者123 更新时间:2023-12-03 02:14:00 24 4
gpt4 key购买 nike

如果您删除 SQL Server 中包含键、索引、约束等的表,它也会删除这些内容吗?我只是想知道当我构建脚本时是否需要为所有这些创建删除脚本,或者我是否可以简单地删除表?

谢谢

最佳答案

是的,尽管您不能删除由另一个表中的外键引用的表。

下面是删除带有在 SQL Server 2008 中引用该表的外键的表的过程:

create table TA ( AID int identity(1, 1), OtherId int, Name varchar(512), constraint PK_TA primary key (AID))
create table TB ( BID int identity(1, 1), OtherId int, Name varchar(512), constraint PK_TB primary key (BID))
alter table TA add constraint FK_TA_TB foreign key (OtherId) references TB (BID)
alter table TB add constraint FK_TB_TA foreign key (OtherId) references TA (AID)

drop table ta -- doesn't work
drop table tb -- doesn't work

create procedure my_DropTable @tableName varchar(512) as
begin
if OBJECT_ID(@tableName) is null begin print 'OBJECT DOES NOT EXIST' return end
declare @sql nvarchar(max)
while exists (select * from sys.foreign_keys where referenced_object_id = object_id(@tableName))
begin
select @sql = 'ALTER TABLE ' + OBJECT_NAME(parent_object_id) + ' DROP CONSTRAINT ' + OBJECT_NAME(object_id)
from sys.foreign_keys where referenced_object_id = object_id(@tableName)

exec sp_executesql @sql
end
set @sql = 'DROP TABLE ' + @tableName
exec sp_executesql @sql
end

exec my_DropTable 'TA'
exec my_DropTable 'TB'

关于SQL Server 删除表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/3680897/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com