Как я могу перечислить все внешние ключи, ссылающиеся на данную таблицу в SQL Server?

Мне нужно удалить таблицу с высокими ссылками в базе данных SQL Server. Как я могу получить список всех ограничений внешнего ключа, которые мне нужно будет удалить, чтобы удалить таблицу?

(SQL-ответы предпочтительнее, чем щелкать в графическом интерфейсе студии управления.)

27.01.2009 12:17:59
См. Как написать все внешние ключи таблицы для помощи. Обновление : ссылка больше не доступна, но соответствующий SQL был скопирован как ответ на связанный вопрос. Вы также можете просматривать зависимости через графический интерфейс.
Galwegian 27.01.2009 12:21:38
26 ОТВЕТОВ
РЕШЕНИЕ

Не знаю, почему никто не предложил, но я использую sp_fkeysдля запроса внешних ключей для данной таблицы:

EXEC sp_fkeys 'TableName'

Вы также можете указать схему:

EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'

Без указания схемы документы заявляют следующее:

Если pktable_owner не указан, применяются правила видимости таблицы по умолчанию базовой СУБД.

В SQL Server, если текущий пользователь владеет таблицей с указанным именем, возвращаются столбцы этой таблицы. Если pktable_owner не указан и текущий пользователь не владеет таблицей с указанным pktable_name, процедура ищет таблицу с указанным pktable_name, принадлежащую владельцу базы данных. Если он существует, столбцы этой таблицы возвращаются.

1114
14.01.2019 18:25:30
Это не работает для меня в базе данных SQL 2008 по некоторым причинам. sp_help показывает отношения, но эта команда не будет.
tbone 5.06.2013 18:43:00
@tbone: у меня была та же проблема, которая была связана с неполным указанием параметров. Для данной таблицы T, принадлежащей O, в базе данных D необходимо выполнить EXEC sp_fkeys \ @ pktable_name = 'T', \ @ pktable_owner = 'O', \ @ pktable_qualifier = 'D' Попробуйте просмотреть выходные данные EXEC sp_tables \ @ table_name = 'T', чтобы выяснить, какими должны быть значения параметров.
Mads Ravn 7.03.2014 09:28:35
@JustinRusso Вы можете обойти это, создав таблицу, сохранить результат в таблице, а затем выбрать конкретные столбцы. Проверьте эту ссылку для примера :).
John Odom 28.04.2015 19:04:23
Отлично работает в SSMS 2014. Спасибо.
AH. 27.08.2015 10:58:57
На это уже ответили в комментариях выше: но просто для ясности - EXEC sp_fkeys @pktable_name = N'Department ', @ pktable_owner = N'dbo'; msdn.microsoft.com/en-NZ/library/ms175090.aspx
Tejas Patel 5.05.2016 00:04:14

Попробуй это :

sp_help 'TableName'
152
22.02.2014 10:24:58
Хороший вспомогательный метод, чтобы узнать, исследуете ли вы свою базу данных вручную. Кроме того, он работает на Azure SQL Server.
Pac0 25.01.2019 13:46:17

Я бы использовал функцию создания диаграмм базы данных в SQL Server Management Studio, но так как вы исключили это - это сработало для меня в SQL Server 2008 (нет 2005).

Получить список ссылающихся таблиц и имен столбцов ...

select 
    t.name as TableWithForeignKey, 
    fk.constraint_column_id as FK_PartNo, c.
    name as ForeignKeyColumn 
from 
    sys.foreign_key_columns as fk
inner join 
    sys.tables as t on fk.parent_object_id = t.object_id
inner join 
    sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where 
    fk.referenced_object_id = (select object_id 
                               from sys.tables 
                               where name = 'TableOthersForeignKeyInto')
order by 
    TableWithForeignKey, FK_PartNo

Чтобы получить имена ограничений внешнего ключа

select distinct name from sys.objects where object_id in 
(   select fk.constraint_object_id from sys.foreign_key_columns as fk
    where fk.referenced_object_id = 
        (select object_id from sys.tables where name = 'TableOthersForeignKeyInto')
)
232
16.09.2016 10:26:03
отлично, хотя использовал referenced_object_id вместо parent. выберите отличное имя из sys.objects, где находится object_id (выберите fk.constraint_object_id из sys.foreign_key_columns как fk, где fk.referenced_object_id = (выберите object_id из sys.tables, где name = 'tablename'))
chillitom 27.01.2009 14:29:11
Вы можете получить имя FK, добавив «имя_объекта (constraint_object_id)» в выбор первого запроса.
sam yi 29.01.2014 21:55:10
Вы можете получить идентификатор объекта object_id ('TableOthersForeignKeyInto')
IvanH 25.04.2016 09:42:17
SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),
       PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME),
       PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME),
       FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),
       FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME),
       FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME),
       -- Force the column to be non-nullable (see SQL BU 325751)
       --KEY_SEQ             = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
       UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),
       PK_NAME = CONVERT(SYSNAME,I.NAME),
       DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLE
FROM   SYS.ALL_OBJECTS O1,
       SYS.ALL_OBJECTS O2,
       SYS.ALL_COLUMNS C1,
       SYS.ALL_COLUMNS C2,
       SYS.FOREIGN_KEYS F
       INNER JOIN SYS.FOREIGN_KEY_COLUMNS K
         ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)
       INNER JOIN SYS.INDEXES I
         ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID
             AND F.KEY_INDEX_ID = I.INDEX_ID)
WHERE  O1.OBJECT_ID = F.REFERENCED_OBJECT_ID
       AND O2.OBJECT_ID = F.PARENT_OBJECT_ID
       AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID
       AND C2.OBJECT_ID = F.PARENT_OBJECT_ID
       AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID
       AND C2.COLUMN_ID = K.PARENT_COLUMN_ID
16
7.08.2009 03:33:10

Отрабатывая то, что сделал @Gishu, я смог произвести и использовать следующий SQL в SQL Server 2005

SELECT t.name AS TableWithForeignKey, fk.constraint_column_id AS FK_PartNo, 
       c.name AS ForeignKeyColumn, o.name AS FK_Name 
  FROM sys.foreign_key_columns AS fk
       INNER JOIN sys.tables AS t ON fk.parent_object_id = t.object_id
       INNER JOIN sys.columns AS c ON fk.parent_object_id = c.object_id 
                                  AND fk.parent_column_id = c.column_id
       INNER JOIN sys.objects AS o ON fk.constraint_object_id = o.object_id
  WHERE fk.referenced_object_id = (SELECT object_id FROM sys.tables 
                                        WHERE name = 'TableOthersForeignKeyInto')
  ORDER BY TableWithForeignKey, FK_PartNo;

Который отображает таблицы, столбцы и имена внешних ключей все в 1 запрос.

3
4.11.2011 01:07:05
SELECT
  object_name(parent_object_id),
  object_name(referenced_object_id),
  name 
FROM sys.foreign_keys
WHERE parent_object_id = object_id('Table Name')
12
29.07.2012 20:25:47
 SELECT OBJECT_NAME(fk.parent_object_id) as ReferencingTable, 
        OBJECT_NAME(fk.constraint_object_id) as [FKContraint]
  FROM sys.foreign_key_columns as fk
 WHERE fk.referenced_object_id = OBJECT_ID('ReferencedTable', 'U')

Это показывает только связь, если есть ограничения внешнего ключа. Моя база данных явно предшествует ограничению FK. В некоторых таблицах используются триггеры для обеспечения ссылочной целостности, а иногда нет ничего, кроме столбца с аналогичным именем, указывающего связь (и вообще никакой ссылочной целостности).

К счастью, у нас есть единая сцена именования, поэтому я могу найти ссылки на таблицы и представления следующим образом:

SELECT OBJECT_NAME(object_id) from sys.columns where name like 'client_id'

Я использовал этот выбор в качестве основы для генерации скрипта, который делает то, что мне нужно сделать для связанных таблиц.

4
19.06.2012 10:25:26

Несколько хороших ответов выше. Но я предпочитаю иметь ответ с одним запросом. Этот кусок кода взят из sys.sp_helpconstraint (sys proc)

Именно так Microsoft смотрит, если есть внешние ключи, связанные с таблицей.

--setup variables. Just change 'Customer' to tbl you want
declare @objid int,
    @objname nvarchar(776)
select @objname = 'Customer'    
select @objid = object_id(@objname)

if exists (select * from sys.foreign_keys where referenced_object_id = @objid)
    select 'Table is referenced by foreign key' =
        db_name() + '.'
        + rtrim(schema_name(ObjectProperty(parent_object_id,'schemaid')))
        + '.' + object_name(parent_object_id)
        + ': ' + object_name(object_id)
    from sys.foreign_keys 
    where referenced_object_id = @objid 
    order by 1

Ответ будет выглядеть так: test_db_name.dbo.Account: FK_Account_Customer

5
25.04.2012 14:28:16
На самом деле это похоже на 4 отдельных оператора запроса ... это фактически делает то же самое в одном выражении: select db_name() + '.' + schema_name(ObjectProperty(parent_object_id,'schemaid')) + '.' + object_name(parent_object_id) + ': ' + object_name(object_id) AS "FK Reference" from sys.foreign_keys where referenced_object_id = object_id('Customer')
hajikelist 6.05.2015 16:55:15

Вы также должны учитывать ссылки на другие объекты.

Если на таблицу ссылались другие таблицы, то, вероятно, на нее также ссылаются другие объекты, такие как представления, хранимые процедуры, функции и многое другое.

Я бы действительно порекомендовал инструмент с графическим интерфейсом, такой как диалоговое окно «Просмотр зависимостей» в SSMS, или бесплатный инструмент, такой как ApexSQL. Найдите его, потому что поиск зависимостей в других объектах может быть подвержен ошибкам, если вы хотите делать это только с SQL.

Если SQL является единственным вариантом, вы можете попробовать сделать это следующим образом.

select O.name as [Object_Name], C.text as [Object_Definition]
from sys.syscomments C
inner join sys.all_objects O ON C.id = O.object_id
where C.text like '%table_name%'
48
1.04.2013 09:10:09

Определить первичные ключи и уникальные ключи для всех таблиц в базе данных ...

Это должно перечислить все ограничения, и в конце вы можете поставить свои фильтры

/* CAST IS DONE , SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT*/
WITH   ALL_KEYS_IN_TABLE (CONSTRAINT_NAME,CONSTRAINT_TYPE,PARENT_TABLE_NAME,PARENT_COL_NAME,PARENT_COL_NAME_DATA_TYPE,REFERENCE_TABLE_NAME,REFERENCE_COL_NAME) 
AS
(
SELECT  CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)) ,
        CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)) ,
        PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)) ,
        PARENT_COL_NAME=CAST ( PKnUKEYCol.name AS VARCHAR(30)) ,
        PARENT_COL_NAME_DATA_TYPE=  oParentColDtl.DATA_TYPE,        
        REFERENCE_TABLE_NAME='' ,
        REFERENCE_COL_NAME='' 

FROM sys.key_constraints as PKnUKEY
    INNER JOIN sys.tables as PKnUTable
            ON PKnUTable.object_id = PKnUKEY.parent_object_id
    INNER JOIN sys.index_columns as PKnUColIdx
            ON PKnUColIdx.object_id = PKnUTable.object_id
            AND PKnUColIdx.index_id = PKnUKEY.unique_index_id
    INNER JOIN sys.columns as PKnUKEYCol
            ON PKnUKEYCol.object_id = PKnUTable.object_id
            AND PKnUKEYCol.column_id = PKnUColIdx.column_id
     INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
            ON oParentColDtl.TABLE_NAME=PKnUTable.name
            AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name
UNION ALL
SELECT  CONSTRAINT_NAME= CAST (oConstraint.name AS VARCHAR(30)) ,
        CONSTRAINT_TYPE='FK',
        PARENT_TABLE_NAME=CAST (oParent.name AS VARCHAR(30)) ,
        PARENT_COL_NAME=CAST ( oParentCol.name AS VARCHAR(30)) ,
        PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,     
        REFERENCE_TABLE_NAME=CAST ( oReference.name AS VARCHAR(30)) ,
        REFERENCE_COL_NAME=CAST (oReferenceCol.name AS VARCHAR(30)) 
FROM sys.foreign_key_columns FKC
    INNER JOIN sys.sysobjects oConstraint
            ON FKC.constraint_object_id=oConstraint.id 
    INNER JOIN sys.sysobjects oParent
            ON FKC.parent_object_id=oParent.id
    INNER JOIN sys.all_columns oParentCol
            ON FKC.parent_object_id=oParentCol.object_id /* ID of the object to which this column belongs.*/
            AND FKC.parent_column_id=oParentCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
    INNER JOIN sys.sysobjects oReference
            ON FKC.referenced_object_id=oReference.id
    INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
            ON oParentColDtl.TABLE_NAME=oParent.name
            AND oParentColDtl.COLUMN_NAME=oParentCol.name
    INNER JOIN sys.all_columns oReferenceCol
            ON FKC.referenced_object_id=oReferenceCol.object_id /* ID of the object to which this column belongs.*/
            AND FKC.referenced_column_id=oReferenceCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/

)

select * from   ALL_KEYS_IN_TABLE
where   
    PARENT_TABLE_NAME  in ('YOUR_TABLE_NAME') 
    or REFERENCE_TABLE_NAME  in ('YOUR_TABLE_NAME')
ORDER BY PARENT_TABLE_NAME,CONSTRAINT_NAME;

Для справки, пожалуйста, прочитайте: http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx

3
4.04.2013 13:27:49
Это содержит слишком много информации для задаваемого вопроса. Не могли бы вы добавить объяснение (и удалить дополнительный код), чтобы просто ответить на вопрос? Вы отправили этот точный ответ на два разных вопроса, и каждому нужен только часть этого ответа.
Andrew Barber 4.04.2013 01:20:20
Я отредактировал ответ - Определите первичные ключи и уникальные ключи для всех таблиц в базе данных ... Я думаю, что здесь ответ уместен, потому что вопрос для всех ссылок.
dekdev 4.04.2013 13:27:14
SELECT
OBJECT_NAME(parent_object_id) 'Parent table',
c.NAME 'Parent column name',
OBJECT_NAME(referenced_object_id) 'Referenced table',
cref.NAME 'Referenced column name'
FROM 
sys.foreign_key_columns fkc 
INNER JOIN 
sys.columns c 
   ON fkc.parent_column_id = c.column_id 
      AND fkc.parent_object_id = c.object_id
INNER JOIN 
sys.columns cref 
   ON fkc.referenced_column_id = cref.column_id 
      AND fkc.referenced_object_id = cref.object_id  where   OBJECT_NAME(parent_object_id) = 'tablename'

Если вы хотите получить отношение внешнего ключа всех таблиц, исключите whereпредложение, иначе напишите ваше имя таблицы вместоtablename

5
18.04.2013 13:18:10

Это дает вам:

  • Сам ФК сам
  • Схема, к которой принадлежит ФК
  • « Таблица ссылок » или таблица, которая имеет FK
  • « Столбец ссылки » или столбец внутри таблицы ссылок, который указывает на FK
  • « Ссылочная таблица » или таблица с ключевым столбцом, на который указывает ваш FK
  • « Ссылочный столбец » или столбец, который является ключом, на который указывает ваш FK

Код ниже:

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
185
21.09.2013 07:14:48
Это лучший ответ на мой взгляд, если вы хотите отфильтровать результаты впоследствии.
Faliorn 20.03.2017 12:19:33
Работает отлично! Было бы еще лучше, если бы вы: а) добавили префикс всех имен столбцов к «Fk» / «ключу»), б) добавили суффикс всех имен столбцов к «имени», в) удалили подчеркивания, г) добавили KeyTableSchemaName, д) добавили значение по умолчанию order by: KeyTableSchemaName, KeyTableName, KeyColumnName, FkTableSchemaName, FkTableName, FkName и f) изменяют порядок столбцов на: KeyTableSchemaName, KeyTableName, KeyColumnName, FkTableSchemaName, FKTableName / Практическое имя / имя-класса, имя_символа, имя_компонента, имя_символа, имя_файма условные обозначения и d / e для наиболее вероятного использования (перечисление FK-зависимостей a Table).
Tom 8.11.2018 18:53:20

Это получает любой внешний ключ, который включает выбранную таблицу. * Предполагает формат _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]' 
1
18.08.2014 19:32:50

Первоначальный вопрос попросил получить список всех внешних ключей в таблицу с высокими ссылками, чтобы ее можно было удалить.

Этот небольшой запрос возвращает все команды «удаления внешнего ключа», необходимые для удаления всех внешних ключей в конкретную таблицу:

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, чтобы получить команды удаления для всех внешних ключей в текущей базе данных.

21
17.06.2015 12:56:46

Я использую это в 2008 году и выше. Это похоже на некоторые другие перечисленные решения, но имена полей правильно обрабатываются для обработки сопоставлений с учетом регистра (LatBin). Кроме того, вы можете указать одно имя таблицы и получить только информацию для этой таблицы.

-->>SPECIFY THE DESIRED DB
USE ???
GO

/*********************************************************************************************

    LIST OUT ALL PRIMARY AND FOREIGN KEY CONSTRAINTS IN A DB OR FOR A SPECIFIED TABLE

*********************************************************************************************/
DECLARE @tblName VARCHAR(255) 

/*******************/

    SET @tblName = NULL-->NULL will return all PK/FK constraints for every table in the database

/*******************/

SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()), 
       PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.schema_id)), 
       PKTABLE_NAME = CONVERT(SYSNAME,O1.name), 
       PKCOLUMN_NAME = CONVERT(SYSNAME,C1.name), 
       FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()), 
       FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.schema_id)), 
       FKTABLE_NAME = CONVERT(SYSNAME,O2.name), 
       FKCOLUMN_NAME = CONVERT(SYSNAME,C2.name), 
       -- Force the column to be non-nullable (see SQL BU 325751) 
       KEY_SEQ             = isnull(convert(smallint,K.constraint_column_id),0), 
       UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.object_id,'CnstIsUpdateCascade')  
                                        WHEN 1 THEN 0 
                                        ELSE 1 
                                      END), 
       DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.object_id,'CnstIsDeleteCascade')  
                                        WHEN 1 THEN 0 
                                        ELSE 1 
                                      END), 
       FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.object_id)), 
       PK_NAME = CONVERT(SYSNAME,I.name), 
       DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLE 
FROM   sys.all_objects O1, 
       sys.all_objects O2, 
       sys.all_columns C1, 
       sys.all_columns C2, 
       sys.foreign_keys F 
       INNER JOIN sys.foreign_key_columns K 
         ON (K.constraint_object_id = F.object_id) 
       INNER JOIN sys.indexes I 
         ON (F.referenced_object_id = I.object_id 
             AND F.key_index_id = I.index_id) 
WHERE  O1.object_id = F.referenced_object_id 
       AND O2.object_id = F.parent_object_id 
       AND C1.object_id = F.referenced_object_id 
       AND C2.object_id = F.parent_object_id 
       AND C1.column_id = K.referenced_column_id
       AND C2.column_id = K.parent_column_id
       AND (   O1.name = @tblName 
            OR O2.name = @tblName
            OR @tblName IS null)
ORDER BY PKTABLE_NAME,FKTABLE_NAME
3
19.03.2015 20:56:46

Я использую этот скрипт, чтобы найти все детали, связанные с внешним ключом. Я использую 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
10
30.08.2015 13:44:12

Существует способ подсчета всех обязанностей для выбранного идентификатора. Просто измените значение @dbTableName, значение @dbRowId и его тип (если int, вам нужно удалить '' в строке № 82 (..SET @SQL = ..)). Наслаждаться.

DECLARE @dbTableName varchar(max) = 'User'
DECLARE @dbRowId uniqueidentifier = '21d34ecd-c1fd-11e2-8545-002219a42e1c'

DECLARE @FK_ROWCOUNT int
DECLARE @SQL nvarchar(max)

DECLARE @PKTABLE_QUALIFIER sysname
DECLARE @PKTABLE_OWNER sysname
DECLARE @PKTABLE_NAME sysname
DECLARE @PKCOLUMN_NAME sysname
DECLARE @FKTABLE_QUALIFIER sysname
DECLARE @FKTABLE_OWNER sysname
DECLARE @FKTABLE_NAME sysname
DECLARE @FKCOLUMN_NAME sysname
DECLARE @UPDATE_RULE smallint
DECLARE @DELETE_RULE smallint
DECLARE @FK_NAME sysname
DECLARE @PK_NAME sysname
DECLARE @DEFERRABILITY sysname

IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL
    DROP TABLE #Temp1;
CREATE TABLE #Temp1 ( 
    PKTABLE_QUALIFIER sysname,
    PKTABLE_OWNER sysname,
    PKTABLE_NAME sysname,
    PKCOLUMN_NAME sysname,
    FKTABLE_QUALIFIER sysname,
    FKTABLE_OWNER sysname,
    FKTABLE_NAME sysname,
    FKCOLUMN_NAME sysname,
    UPDATE_RULE smallint,
    DELETE_RULE smallint,
    FK_NAME sysname,
    PK_NAME sysname,
    DEFERRABILITY sysname,
    FK_ROWCOUNT int
    );
DECLARE FK_Counter_Cursor CURSOR FOR
    SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),
       PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME),
       PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME),
       FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),
       FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME),
       FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME),
       -- Force the column to be non-nullable (see SQL BU 325751)
       --KEY_SEQ             = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
       UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),
       PK_NAME = CONVERT(SYSNAME,I.NAME),
       DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLE
    FROM   SYS.ALL_OBJECTS O1,
           SYS.ALL_OBJECTS O2,
           SYS.ALL_COLUMNS C1,
           SYS.ALL_COLUMNS C2,
           SYS.FOREIGN_KEYS F
           INNER JOIN SYS.FOREIGN_KEY_COLUMNS K
             ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)
           INNER JOIN SYS.INDEXES I
             ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID
                 AND F.KEY_INDEX_ID = I.INDEX_ID)
    WHERE  O1.OBJECT_ID = F.REFERENCED_OBJECT_ID
           AND O2.OBJECT_ID = F.PARENT_OBJECT_ID
           AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID
           AND C2.OBJECT_ID = F.PARENT_OBJECT_ID
           AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID
           AND C2.COLUMN_ID = K.PARENT_COLUMN_ID
           AND O1.NAME = @dbTableName
OPEN FK_Counter_Cursor;
FETCH NEXT FROM FK_Counter_Cursor INTO @PKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY;
WHILE @@FETCH_STATUS = 0
   BEGIN
        SET @SQL = 'SELECT @dbCountOut = COUNT(*) FROM [' + @FKTABLE_NAME + '] WHERE [' + @FKCOLUMN_NAME + '] = ''' + CAST(@dbRowId AS varchar(max)) + '''';
        EXECUTE sp_executesql @SQL, N'@dbCountOut int OUTPUT', @dbCountOut = @FK_ROWCOUNT OUTPUT;
        INSERT INTO #Temp1 (PKTABLE_QUALIFIER, PKTABLE_OWNER, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, FKCOLUMN_NAME, UPDATE_RULE, DELETE_RULE, FK_NAME, PK_NAME, DEFERRABILITY, FK_ROWCOUNT) VALUES (@FKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY, @FK_ROWCOUNT)
      FETCH NEXT FROM FK_Counter_Cursor INTO @PKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY;
   END;
CLOSE FK_Counter_Cursor;
DEALLOCATE FK_Counter_Cursor;
GO
SELECT * FROM #Temp1
GO
2
20.10.2015 14:49:57

Вот код 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, поэтому давайте рассмотрим пример.

Итак, предположим, что я хотел удалить Employeesтаблицу из любимой Northwindбазы данных Microsoft , но SQL Server сказал мне, что один или несколько внешних ключей мешают мне сделать это.

Команда SQL выше вернет эти результаты ...

Внешние ключи

Это показывает мне, что есть 3 внешних ключа, которые ссылаются на Employeesтаблицу. Другими словами, мне не разрешили бы удалить (удалить) эту таблицу, пока эти три Внешних ключа не будут сначала удалены.

В результатах первая строка показывает, как в результатах будет показано следующее ограничение внешнего ключа.

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])

Со всеми этими командами у вас есть все необходимое для удаления соответствующих внешних ключей, чтобы вы могли удалить таблицу, а затем воссоздать их позже.

Уф. Надеюсь это поможет.

17
4.05.2017 09:23:00
Было бы понятнее, если бы вы использовали внутренние объединения и предложения вместо перекрестных объединений. Но это было полезно, тем не менее!
TamusJRoyce 19.12.2017 19:47:07

Первый

EXEC sp_fkeys 'Table', 'Schema'

Затем используйте NimbleText, чтобы поиграть с вашими результатами

8
16.10.2017 12:28:53

У меня работает следующее решение:

--Eliminar las llaves foraneas
declare @query varchar(8000)
declare cursorRecorrerTabla cursor for

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


--3ro. abrir el cursor.
open cursorRecorrerTabla
fetch next from cursorRecorrerTabla
into @query
while @@fetch_status = 0
begin
--inicio cuerpo del cursor
    print @query
    exec(@query)
--fin cuerpo del cursor
fetch next from cursorRecorrerTabla
into @query
end
--cerrar cursor
close cursorRecorrerTabla
deallocate cursorRecorrerTabla
1
12.07.2018 14:53:55

Самый простой из них - использование sys.foreign_keys_columns в SQL. Здесь таблица содержит идентификаторы объектов всех внешних ключей по идентификатору ссылочной колонки, идентификатору ссылочной таблицы, а также ссылочным столбцам и таблицам. Поскольку идентификатор остается постоянным, результат будет надежным для дальнейших изменений в схеме, а также в таблицах.

Запрос:

SELECT    
OBJECT_NAME(fkeys.constraint_object_id) foreign_key_name
,OBJECT_NAME(fkeys.parent_object_id) referencing_table_name
,COL_NAME(fkeys.parent_object_id, fkeys.parent_column_id) referencing_column_name
,OBJECT_SCHEMA_NAME(fkeys.parent_object_id) referencing_schema_name
,OBJECT_NAME (fkeys.referenced_object_id) referenced_table_name
,COL_NAME(fkeys.referenced_object_id, fkeys.referenced_column_id) 
referenced_column_name
,OBJECT_SCHEMA_NAME(fkeys.referenced_object_id) referenced_schema_name
FROM sys.foreign_key_columns AS fkeys

Мы также можем добавить фильтр с помощью «где»

WHERE OBJECT_NAME(fkeys.parent_object_id) = 'table_name' AND 
OBJECT_SCHEMA_NAME(fkeys.parent_object_id) = 'schema_name'
15
22.10.2018 07:30:41
Это отлично подходит для случаев, когда вам нужно удалить целые конструкции БД / наборы ссылочных таблиц.
Morvael 16.11.2018 09:52:25

Mysql сервер имеет information_schema.REFERENTIAL_CONSTRAINTSтаблицу FYI, вы можете отфильтровать ее по имени таблицы или по названию таблицы.

2
6.05.2019 09:16:05

Вы можете найти через запрос ниже:

 SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table', 
      OBJECT_NAME(FK.parent_object_id) 'Referring Table', FK.name 'Foreign Key', 
      COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
      COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
     FROM sys.foreign_keys AS FK
             INNER JOIN sys.foreign_key_columns AS FKC 
                 ON FKC.constraint_object_id = FK.OBJECT_ID
     WHERE OBJECT_NAME (FK.referenced_object_id) = 'YourTableName'
     AND COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) = 'YourColumnName'
     order by  OBJECT_NAME(FK.parent_object_id)
0
20.06.2019 09:27:56

Также попробуйте.

EXEC sp_fkeys 'tableName', 'schemaName'

с sp_fkeysвами может фильтровать результат, не только рк имени таблицы и схемы , но и с Рк именем таблицы и схемы. ссылка на сайт

0
18.09.2019 10:11:17

Самый предпочтительный ответ от @BankZ

sp_help 'TableName'   

дополнительно для другой схемы

sp_help 'schemaName.TableName'   
0
22.11.2019 20:56:14

Список всех внешних ключей, ссылающихся на данную таблицу в SQL Server:

Вы можете получить имя таблицы ссылок и имя столбца с помощью следующего запроса ...

SELECT 
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM 
   sys.foreign_keys AS f
INNER JOIN 
   sys.foreign_key_columns AS fc 
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE 
   OBJECT_NAME (f.referenced_object_id) = 'TableName'

И следующий скриншот для вашего понимания ...

введите описание изображения здесь

2
27.11.2019 13:20:49