我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?

(SQL比在管理工作室的GUI中点击更可取)


当前回答

这将获得涉及所选表的任何外键。*假设为_FIRSTABLENAME_SECONDTABLENAME格式。

 declare @tablename as varchar(MAX)
 SET @tablename = 'yourtablename'
 SELECT name
 FROM YOURDATABASE.sys.objects
 WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT' and (name LIKE '%_' + @tablename + 'empdb_%' or name LIKE '%_' + @tablename )

这是更一般的形式:

 SELECT name
 FROM YOURDATABASE_PROD.sys.objects
 WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT' and name LIKE '%' + @tablename + '%' and
 name NOT LIKE '[a-zA-Z0-9]' + @tablename + '%' and name NOT LIKE '%' + @tablename + '[a-zA-Z0-9]' 

其他回答

最初的问题要求将所有外键的列表放入一个高度引用的表中,以便可以删除表。

这个小查询返回将所有外键放入特定表所需的“drop foreign key”命令:

SELECT 
   'ALTER TABLE ['+sch.name+'].['+referencingTable.Name+'] DROP CONSTRAINT ['+foreignKey.name+']' '[DropCommand]'
FROM sys.foreign_key_columns fk
    JOIN sys.tables referencingTable ON fk.parent_object_id = referencingTable.object_id
    JOIN sys.schemas sch ON referencingTable.schema_id = sch.schema_id
    JOIN sys.objects foreignKey ON foreignKey.object_id = fk.constraint_object_id
    JOIN sys.tables referencedTable ON fk.referenced_object_id = referencedTable.object_id
WHERE referencedTable.name = 'MyTableName'

示例输出:

[DropCommand]
ALTER TABLE [dbo].[OtherTable1] DROP CONSTRAINT [FK_OtherTable1_MyTable]
ALTER TABLE [dbo].[OtherTable2] DROP CONSTRAINT [FK_OtherTable2_MyTable]

省略where -子句以获取当前数据库中所有外键的删除命令。

我正在使用这个脚本来查找与外键相关的所有细节。 我正在使用INFORMATION.SCHEMA。 下面是一个SQL脚本:

SELECT 
    ccu.table_name AS SourceTable
    ,ccu.constraint_name AS SourceConstraint
    ,ccu.column_name AS SourceColumn
    ,kcu.table_name AS TargetTable
    ,kcu.column_name AS TargetColumn
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
    INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
        ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME 
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
        ON kcu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME  
ORDER BY ccu.table_name

下面是我将使用的SQL代码。

SELECT 
   f.name AS 'Name of Foreign Key',
   OBJECT_NAME(f.parent_object_id) AS 'Table name',
   COL_NAME(fc.parent_object_id,fc.parent_column_id) AS 'Fieldname',
   OBJECT_NAME(t.object_id) AS 'References Table name',
   COL_NAME(t.object_id,fc.referenced_column_id) AS 'References fieldname',

   'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id) + ']  DROP CONSTRAINT [' + f.name + ']' AS 'Delete foreign key',

   'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id) + ']  WITH NOCHECK ADD CONSTRAINT [' + 
        f.name + '] FOREIGN KEY([' + COL_NAME(fc.parent_object_id,fc.parent_column_id) + ']) REFERENCES ' + 
        '[' + OBJECT_NAME(t.object_id) + '] ([' +
        COL_NAME(t.object_id,fc.referenced_column_id) + '])' AS 'Create foreign key'
    -- , delete_referential_action_desc AS 'UsesCascadeDelete'
FROM sys.foreign_keys AS f,
     sys.foreign_key_columns AS fc,
     sys.tables t 
WHERE f.OBJECT_ID = fc.constraint_object_id
AND t.OBJECT_ID = fc.referenced_object_id
AND OBJECT_NAME(t.object_id) = 'Employees'      --  Just show the FKs which reference a particular table
ORDER BY 2

这不是特别清晰的SQL,所以让我们看一个例子。

所以,假设我想删除Microsoft心爱的Northwind数据库中的Employees表,但是SQL Server告诉我一个或多个外键阻止我这样做。

上面的SQL命令将返回这些结果…

它显示有3个外键引用雇员表。换句话说,除非先删除这三个外键,否则不允许删除(删除)这个表。

在结果中,第一行是以下外键约束在结果中的显示方式。

ALTER TABLE [dbo].[Employees]  WITH NOCHECK 
ADD CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo])
REFERENCES [dbo].[Employees] ([EmployeeID])

倒数第二列显示了我需要使用的SQL命令来删除这些外键之一,例如:

ALTER TABLE [Employees] DROP CONSTRAINT [FK_Employees_Employees]

...右边一列显示了创建它的SQL…

ALTER TABLE [Employees] WITH NOCHECK 
ADD CONSTRAINT [FK_Employees_Employees] 
FOREIGN KEY([ReportsTo]) REFERENCES [Employees] ([EmployeeID])

使用所有这些命令,您就拥有了删除相关外键所需的一切,从而可以删除表,然后再重新创建它们。

唷。希望这能有所帮助。

SELECT
  object_name(parent_object_id),
  object_name(referenced_object_id),
  name 
FROM sys.foreign_keys
WHERE parent_object_id = object_id('Table Name')
with tab_list as (
    select t.name AS Table_Name, t.object_id, s.name AS Table_Schema  from sys.tables t, sys.schemas s 
     where t.schema_id = s.schema_id
       and s.name = 'your schema') 
select IIF(col.column_id = 1, tab.TABLE_SCHEMA + '.' + tab.TABLE_NAME, NULL) Table_Name,
       col.Name AS Column_Name, IIF(col.IS_NULLABLE= 0, 'NOT NULL', '') Nullable, st.name Type,
       CASE WHEN st.name = 'decimal' THEN CONVERT(NVARCHAR(4000), col.Precision) + ',' + CONVERT(NVARCHAR(4000), col.Scale) 
            WHEN col.max_length = -1 THEN 'max'
            WHEN st.name in ('int', 'bit', 'bigint', 'datetime2') THEN NULL
       ELSE CONVERT(NVARCHAR(4000), col.max_length / 2)
       END
       AS Length,
       ss.name + '.' + stab.name Referenced_Table, scol.name Referenced_Column 
from sys.COLUMNS col  
    INNER JOIN tab_list tab ON col.object_id = tab.object_id
    INNER JOIN sys.types st ON col.system_type_id = st.system_type_id AND col.user_type_id = st.user_type_id 
    LEFT JOIN [sys].[foreign_key_columns] sfkc ON col.object_id = sfkc.parent_object_id AND col.column_id = sfkc.parent_column_id
    LEFT JOIN sys.tables stab ON sfkc.referenced_object_id = stab.object_id
    LEFT JOIN sys.columns scol ON sfkc.referenced_object_id = scol.object_id AND sfkc.referenced_column_id = scol.column_id 
    LEFT JOIN sys.schemas ss ON ss.schema_id = stab.schema_id