How can I list all foreign keys referencing a given table in SQL Server?

I need to remove a highly referenced table in a SQL Server database. How can I get a list of all the foreign key constraints I will need to remove in order to drop the table?

(SQL answers preferable over clicking about in the GUI of the management studio.)

28 thoughts on “How can I list all foreign keys referencing a given table in SQL Server?

  1. user

    This gets any foreign key that involves the chosen table. *Assumes a _FIRSTABLENAME_SECONDTABLENAME format.

     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 )
    

    This is a more general form:

     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]' 
    
    Reply
  2. user

    This gives you:

    • The FK itself itself
    • Schema that the FK belongs to
    • The “referencing table” or the table that has the FK
    • The “referencing column” or the column inside referencing table that points to the FK
    • The “referenced table” or the table that has the key column that your FK is pointing to
    • The “referenced column” or the column that is the key that your FK is pointing to

    Code below:

    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
    
    Reply
  3. user

    Not sure why no one suggested but I use sp_fkeys to query foreign keys for a given table:

    EXEC sp_fkeys 'TableName'
    

    You can also specify the schema:

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

    Without specifying the schema, the docs state the following:

    If pktable_owner is not specified, the default table visibility rules
    of the underlying DBMS apply.

    In SQL Server, if the current user owns a table with the specified
    name, that table’s columns are returned. If pktable_owner is not
    specified and the current user does not own a table with the specified
    pktable_name, the procedure looks for a table with the specified
    pktable_name owned by the database owner. If one exists, that table’s
    columns are returned.

    Reply
  4. user

    Determine primary keys and unique keys for all tables in a database…

    This should list all the constraints and at the end you can put your filters

    /* 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;
    

    For reference please read thru – http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx

    Reply
  5. user
    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'
    

    If you want to get the foreign key relation of all the tables exclude the where clause else write your tablename instead of tablename

    Reply
  6. user

    You should also mind the references to other objects.

    If the table was highly referenced by other tables than it’s probably also highly referenced by other objects such as views, stored procedures, functions and more.

    I’d really recommend GUI tool such as ‘view dependencies’ dialog in SSMS or free tool like ApexSQL Search for this because searching for dependencies in other objects can be error prone if you want to do it only with SQL.

    If SQL is the only option you could try doing it like this.

    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%'
    
    Reply
  7. user

    Some good answers above. But I prefer to have the answer with one query.
    This piece of code is taken from sys.sp_helpconstraint (sys proc)

    That’s the way Microsoft looks up if there are foreign keys associated to the tbl.

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

    The answer will look like this: test_db_name.dbo.Account: FK_Account_Customer

    Reply
  8. user
     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')
    

    This only shows the relationship if the are foreign key constraints. My database apparently predates the FK constraint.Some table use triggers to enforce referential integrity, and sometimes there’s nothing but a similarly named column to indicate the relationship (and no referential integrity at all).

    Fortunately, we do have a consistent naming scene so I am able to find referencing tables
    and views like this:

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

    I used this select as the basis for generating a script the does what I need to do on
    the related tables.

    Reply
  9. user

    Working off of what @Gishu did I was able to produce and use the following SQL in 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;
    

    Which Displays the tables, columns and Foreign Key names all in 1 query.

    Reply
  10. user
    SELECT
      object_name(parent_object_id),
      object_name(referenced_object_id),
      name 
    FROM sys.foreign_keys
    WHERE parent_object_id = object_id('Table Name')
    
    Reply
  11. user

    I’d use the Database Diagramming feature in SQL Server Management Studio, but since you ruled that out – this worked for me in SQL Server 2008 (don’t have 2005).

    To get list of referring table and column names…

    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
    

    To get names of foreign key constraints

    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')
    )
    
    Reply
  12. user
    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
    
    Reply
  13. user

    The original question asked to get a list of all foreign keys into a highly referenced table so that the table can be removed.

    This little query returns all the ‘drop foreign key’ commands needed to drop all foreign keys into a particular table:

    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'
    

    Example output:

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

    Omit the WHERE-clause to get the drop commands for all foreign keys in the current database.

    Reply
  14. user

    I have been using this on 2008 and up. It’s similar to some other solutions listed but, the field names are proper cased to handle case specific (LatBin) collations. Additionally, you can feed it a single table name and retrieve just the info for that table.

    -->>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
    
    Reply
  15. user

    I am using this script to find all details related to foreign key.
    I am using INFORMATION.SCHEMA.
    Below is a SQL Script:

    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
    
    Reply
  16. user

    There is how to get count of all responsibilities for selected Id. Just change @dbTableName value, @dbRowId value and its type (if int you need to remove ” in line no 82 (..SET @SQL = ..)). Enjoy.

    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
    
    Reply
  17. user

    Here’s the SQL code I would use.

    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
    

    It’s not particularly clear SQL, so let’s look at an example.

    So, supposing I wanted to drop the Employees table in Microsoft’s beloved Northwind database, but SQL Server told me that one or more Foreign Keys were preventing me from doing this.

    The SQL command above would return these results…

    Foreign Keyes

    It shows me that there are 3 Foreign Keys which reference the Employees table. In other words, I wouldn’t be allowed to delete (drop) this table until these three Foreign Keys are first deleted.

    In the results, the first row is how the following Foreign Key constraint would be shown in the results.

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

    The second-to-last column shows the SQL command I would need to use to delete one of these Foreign Keys, eg:

    ALTER TABLE [Employees] DROP CONSTRAINT [FK_Employees_Employees]
    

    …and the right-hand column shows the SQL to create it…

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

    With all of these commands, you have everything you need to delete the relevant Foreign Keys to allow you to delete a table, then recreate them later.

    Phew. Hope this helps.

    Reply
  18. user

    The following solution work for me:

    --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
    
    Reply
  19. user

    The most Simplest one is by using sys.foreign_keys_columns in SQL. Here the table contains the Object ids of all the foreign keys wrt their Referenced column ID Referenced Table ID as well as the Referencing Columns and Tables. As the Id’s remains constant the result will be reliable for further modifications in Schema as well as tables.

    Query:

    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
    

    We can also add filter by using ‘where’

    WHERE OBJECT_NAME(fkeys.parent_object_id) = 'table_name' AND 
    OBJECT_SCHEMA_NAME(fkeys.parent_object_id) = 'schema_name'
    
    Reply
  20. user

    Mysql server has information_schema.REFERENTIAL_CONSTRAINTS table FYI, you can filter it by table name or referenced table name.

    Reply
  21. user

    You can find through below query :

     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)
    
    Reply
  22. user

    List of all foreign keys referencing a given table in SQL Server :

    You can get the referencing table name and column name through following query…

    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'
    

    And following screenshot for your understanding…

    enter image description here

    Reply
  23. user

    Also try.

    EXEC sp_fkeys 'tableName', 'schemaName'
    

    with sp_fkeys you may filter the result by not only pk table name and schema but also with fk table name and schema. link

    Reply
  24. user
    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
    
    Reply

Leave a Reply

Your email address will not be published.