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


Solution 1:  
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'

Solution 2: 


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

For adding foreign key using simple alter query below is the syntax:

alter table variants
add foreign key(fuelid) references fuels(fuelid)

By : Ziaur Rab

Post a Comment

9 Comments

  1. I found really intresting stuff here..Thank you
    award winning wedding photographer

    ReplyDelete
  2. I found really intresting stuff here..Thank you
    Actual day photographer

    ReplyDelete
  3. I found really intresting stuff here..Thank you
    portable shower and toilet

    ReplyDelete
  4. I found really intresting stuff here..Thank you
    mobile toilet for rent

    ReplyDelete
  5. I found really intresting stuff here..Thank you
    funeral service singapore

    ReplyDelete
  6. I found really intresting stuff here..Thank you
    piano lessons singapore

    ReplyDelete
  7. I found really intresting stuff here..Thank you
    piano teacher singapore


    ReplyDelete
  8. I found really intresting stuff here..Thank you
    Virtual team building

    ReplyDelete
  9. Thank you very much for writing such an interesting article on this topic.

    Would you like to create an App Like Groupon Mobile App ? then we have the best solution for you by The App Ideas .

    ReplyDelete