ChatGPT解决这个技术问题 Extra ChatGPT

如果我想像这样删除数据库中的所有表,它会处理外键约束吗?如果没有,我该如何处理?

GO
IF OBJECT_ID('dbo.[Course]','U') IS NOT NULL
    DROP TABLE dbo.[Course]
GO
IF OBJECT_ID('dbo.[Student]','U') IS NOT NULL
    DROP TABLE dbo.[Student]
检查这个答案,它对我有帮助;)stackoverflow.com/a/5488670/2205144

C
Community

不,如果确实有外键引用它,这不会删除您的表。

要获取引用您的表的所有外键关系,您可以使用此 SQL(如果您使用的是 SQL Server 2005 及更高版本):

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

如果有的话,使用这里的语句,您可以创建 SQL 语句来实际删除这些 FK 关系:

SELECT 
    'ALTER TABLE [' +  OBJECT_SCHEMA_NAME(parent_object_id) +
    '].[' + OBJECT_NAME(parent_object_id) + 
    '] DROP CONSTRAINT [' + name + ']'
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

注意:生成的表包含删除要在不同查询中运行的各个约束的语句。在我的情况下,它产生了奇迹,因为我需要摆脱一个无用的表(用于测试目的),同时保持其他具有 FK 的表完好无损。
我不得不使用 parent_object_id 而不是 referenced_object_id
获取所有引用表的 SELECT 语句的替代方法是: EXEC sp_fkeys 'Student';
小调整此查询 SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [name] ' FROM sys.foreign_keys WHERE referenced_object_id = object_id('Student') 替换 [name]带有约束名称
如何将您的最后一个选择分配为变量并执行它?
S
Stephan Bauer

在 SQL Server Management Studio 2008 (R2) 和更新版本中,您可以右键单击

DB -> 任务 -> 生成脚本

选择要删除的表。

选择“保存到新的查询窗口”。

单击高级按钮。

将 Script DROP 和 CREATE 设置为 Script DROP。

将脚本外键设置为 True。

单击确定。

单击下一步 -> 下一步 -> 完成。

查看脚本,然后执行。


仍然是相同的 'Could not drop object 'my_table' 因为它被 FOREIGN KEY 约束引用。
这个答案如何获得 28 + 评级超出了我的范围......仍然不起作用,伙计
虽然它确实生成了脚本 - 该脚本并没有解决问题,即删除外键约束引用的表。
SQL 2019 运行良好,具有外键约束,通过它们没有问题。
P
Philip Kelley

如果您首先删除“子”表,则外键也将被删除。如果您首先尝试删除“父”表,您将收到“无法删除对象 'a',因为它被 FOREIGN KEY 约束引用”。错误。


是的,但没有解决方案。孩子可以有其他表的外键和/或您可能不想首先删除它。
是的,如果目标是,如前所述,“如果我想删除我的数据库中的所有表......”的解决方案
鉴于提问者想要“删除我数据库中的所有表”,这比接受的答案要好得多
K
Krait

这是使用 sp_MSdropconstraints 过程正确删除所有表的另一种方法。我能想到的最短代码:

exec sp_MSforeachtable "declare @name nvarchar(max); set @name = parsename('?', 1); exec sp_MSdropconstraints @name";
exec sp_MSforeachtable "drop table ?";

这确实在这简单的 2 行的帮助下回答了原始问题“如果我想删除数据库中的所有表”。至少对于我的 SQLServer 2019 而言。谢谢。
S
Shiraz Bhaiji

如果是 SQL Server,则必须先删除约束,然后才能删除表。


S
Stas Svishov

@mark_s 发布的内容稍微更通用的版本,这对我有帮助

SELECT 
'ALTER TABLE ' +  OBJECT_SCHEMA_NAME(k.parent_object_id) +
'.[' + OBJECT_NAME(k.parent_object_id) + 
'] DROP CONSTRAINT ' + k.name
FROM sys.foreign_keys k
WHERE referenced_object_id = object_id('your table')

只需插入您的表名,然后执行它的结果。


嗨,我一直在尝试通过上述方式删除我的外键约束,但是当我之后去删除一个表时,它一直说“无法删除该对象,因为它仍然被外键约束引用......有什么想法吗?提前致谢。
W
Warren Rumak

这是删除表本身后面的所有约束的另一种方法,使用涉及 FOR XML PATH('') 的连接技巧,该技巧允许将多个输入行合并到单个输出行中。应该适用于任何 SQL 2005 &之后。

为了安全起见,我已将 EXECUTE 命令注释掉。

DECLARE @SQL NVARCHAR(max)
;WITH fkeys AS (
    SELECT quotename(s.name) + '.' + quotename(o.name) tablename, quotename(fk.name) constraintname 
    FROM sys.foreign_keys fk
    JOIN sys.objects o ON fk.parent_object_id = o.object_id
    JOIN sys.schemas s ON o.schema_id = s.schema_id
)
SELECT @SQL = STUFF((SELECT '; ALTER TABLE ' + tablename + ' DROP CONSTRAINT ' + constraintname
FROM fkeys
FOR XML PATH('')),1,2,'')

-- EXECUTE(@sql)

SELECT @SQL = STUFF((SELECT '; DROP TABLE ' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME) 
FROM INFORMATION_SCHEMA.TABLES 
FOR XML PATH('')),1,2,'')

-- EXECUTE(@sql)

这不能很好地工作,因为并非所有 FK 都会被删除(只有那些我们的表被用作父级的地方,我们也应该删除我们的表被用作“引用”的约束)。
Roman,原帖是关于删除外键约束的。我的回答并不旨在涵盖除此之外的任何内容。
D
David Hoelzer

这是实现解决方案的完整脚本:

create Procedure [dev].DeleteTablesFromSchema
(
    @schemaName varchar(500)
)
As 
begin
    declare @constraintSchemaName nvarchar(128), @constraintTableName nvarchar(128),  @constraintName nvarchar(128)
    declare @sql nvarchar(max)
    -- delete FK first
    declare cur1 cursor for
    select distinct 
    CASE WHEN t2.[object_id] is NOT NULL  THEN  s2.name ELSE s.name END as SchemaName,
    CASE WHEN t2.[object_id] is NOT NULL  THEN  t2.name ELSE t.name END as TableName,
    CASE WHEN t2.[object_id] is NOT NULL  THEN  OBJECT_NAME(d2.constraint_object_id) ELSE OBJECT_NAME(d.constraint_object_id) END as ConstraintName
    from sys.objects t 
        inner join sys.schemas s 
            on t.[schema_id] = s.[schema_id]
        left join sys.foreign_key_columns d 
            on  d.parent_object_id = t.[object_id]
        left join sys.foreign_key_columns d2 
            on  d2.referenced_object_id = t.[object_id]
        inner join sys.objects t2 
            on  d2.parent_object_id = t2.[object_id]
        inner join sys.schemas s2 
            on  t2.[schema_id] = s2.[schema_id]
    WHERE t.[type]='U' 
        AND t2.[type]='U'
        AND t.is_ms_shipped = 0 
        AND t2.is_ms_shipped = 0 
        AND s.Name=@schemaName
    open cur1
    fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
    while @@fetch_status = 0
    BEGIN
        set @sql ='ALTER TABLE ' + @constraintSchemaName + '.' + @constraintTableName+' DROP CONSTRAINT '+@constraintName+';'
        exec(@sql)
        fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
    END
    close cur1
    deallocate cur1

    DECLARE @tableName nvarchar(128)
    declare cur2 cursor for
    select s.Name, p.Name
    from sys.objects p
        INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id]
    WHERE p.[type]='U' and is_ms_shipped = 0 
    AND s.Name=@schemaName
    ORDER BY s.Name, p.Name
    open cur2

    fetch next from cur2 into @schemaName,@tableName
    while @@fetch_status = 0
    begin
        set @sql ='DROP TABLE ' + @schemaName + '.' + @tableName
        exec(@sql)
        fetch next from cur2 into @schemaName,@tableName
    end

    close cur2
    deallocate cur2

end
go

C
CodeTzu
Removing Referenced FOREIGN KEY Constraints
Assuming there is a parent and child table Relationship in SQL Server:

--First find the name of the Foreign Key Constraint:
  SELECT * 
  FROM sys.foreign_keys
  WHERE referenced_object_id = object_id('States')

--Then Find foreign keys referencing to dbo.Parent(States) table:
   SELECT name AS 'Foreign Key Constraint Name', 
           OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) AS 'Child Table'
   FROM sys.foreign_keys 
   WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND 
              OBJECT_NAME(referenced_object_id) = 'dbo.State'

 -- Drop the foreign key constraint by its name 
   ALTER TABLE dbo.cities DROP CONSTRAINT FK__cities__state__6442E2C9;

 -- You can also use the following T-SQL script to automatically find 
 --and drop all foreign key constraints referencing to the specified parent 
 -- table:

 BEGIN

DECLARE @stmt VARCHAR(300);

-- Cursor to generate ALTER TABLE DROP CONSTRAINT statements  
 DECLARE cur CURSOR FOR
 SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.' + 
 OBJECT_NAME(parent_object_id) +
                ' DROP CONSTRAINT ' + name
 FROM sys.foreign_keys 
 WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND 
            OBJECT_NAME(referenced_object_id) = 'states';

 OPEN cur;
 FETCH cur INTO @stmt;

 -- Drop each found foreign key constraint 
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC (@stmt);
    FETCH cur INTO @stmt;
  END

  CLOSE cur;
  DEALLOCATE cur;

  END
  GO

--Now you can drop the parent table:

 DROP TABLE states;
--# Command(s) completed successfully.

Ф
Фарид

一切都简单得多。有一个配置可以关闭检查并打开它。

例如,如果你使用 MySQL,那么要关闭它,你必须写 SET foreign_key_checks = 0;

然后删除或清表,重新启用检查 SET foreign_key_checks = 1;


H
Hazza

使用 SQL Server 管理器,您可以从 UI 中删除外键约束。如果您想删除表 Diary 但 User 表有一个外键 DiaryId 指向 Diary 表,您可以展开(使用加号)User 表,然后展开 Foreign Keys部分。右键单击指向日记表的外键,然后选择 Delete。然后您可以展开 Columns 部分,右键单击并删除列 DiaryId。然后你可以运行:

drop table Diary

我知道您的实际问题是关于删除所有表,所以这对于这种情况可能没有用。但是,如果您只想删除几个表,我相信这很有用(标题没有明确提到删除所有表)。


j
jithil

执行以下代码以获取阻止您放置的外键约束名称。例如,我采用 roles 表。

      SELECT *
      FROM sys.foreign_keys
      WHERE referenced_object_id = object_id('roles');

      SELECT name AS 'Foreign Key Constraint Name',
      OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id)
      AS 'Child Table' FROM sys.foreign_keys
      WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo'
      AND OBJECT_NAME(referenced_object_id) = 'dbo.roles'

您将获得如下 FK 名称:FK__Table1__roleId__1X1H55C1

现在运行下面的代码来删除从上面得到的 FK 引用。

ALTER TABLE dbo.users drop CONSTRAINT FK__Table1__roleId__1X1H55C1;

完毕!


C
Chris Catignani

找到所有外键.. 编写它们

SELECT *  FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

然后从子表中删除外键。现在您可以删除父表。

如果您想重新创建父表,请确保运行您之前创建的脚本。


D
DarkTrick

如果您在 mysql 服务器(不是 MSSQL)上并且不介意丢失表,则可以使用简单的查询一次删除多个表:

SET foreign_key_checks = 0;
DROP TABLE IF EXISTS table_a,table_b,table_c,table_etc;
SET foreign_key_checks = 1;

这样,您在查询中使用表的顺序无关紧要。

如果有人要说如果您有一个包含许多表的数据库这不是一个好的解决方案这一事实:我同意!


我得到Incorrect syntax near '='. (102) (SQLExecDirectW)
@Matt Bit 很难猜出哪个 '=' 你得到了那个错误。
foreign_key_checks 在 MSSQL 服务器上不起作用。我认为这是一个 MySql 特定的变量。
@ooXei1sh 不,不在 MSSQL 上。这就是为什么我在文章开头说:“如果你在 MYSQL 服务器上”。
M
Musakkhir Sayyed

如果您想DROP使用外键使用已被其他表引用的表

DROP TABLE *table_name* CASCADE CONSTRAINTS;
我认为它应该适合您。


sql server 中没有 cascade constraints
A
Andomar

如果我想删除数据库中的所有表

然后删除整个数据库要容易得多:

DROP DATABASE WorkerPensions

为此给你一个-1,因为它不是问题的有效答案,有两个重要原因:1)它删除的不仅仅是表格!存储过程、函数、UDT、安全性、.NET 程序集等都随着 DROP DATABASE 而消失。 2) 可能不允许您创建数据库,例如集中管理的开发环境,其中数据库由 IT 提供,并且在创建时有您不知道的额外要求。