SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database

SQL SERVER - 2005 - Find Tables With Foreign Key Constraint in Database keyicon While writing articles based on my Find Table without Clustered Index – Find Table with no Primary Key I got an idea about writing this article. I was thinking if you can find primary key for any table in the database, you can sure find foreign key for any table in the database as well. If you have database and it is not forcing a database relationship, I suggest you look at your design once again. Relational database without keys and connection, may not make sense at all.

The keys are very, very critical to any database system as they are the one who enforces the system

In SQL Server 2005 How to Find Tables With Foreign Key Constraint in Database?

Script to find all the primary key constraint in database:

USE AdventureWorks;
GO
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

In SQL Server 2005 How to Find Tables With Primary Key Constraint in Database?
Find Tables With Primary Key Constraint in Database

Please leave a comment right below.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text
Next Post
SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database

Related Posts

48 Comments. Leave new

  • bharath kurra
    July 18, 2013 8:38 am

    hi
    i had a problem..
    suppose a primary table had a foreign key with Secondary table.how can we know the data present in primary table is present secondary table or not?

    Reply
    • You can do this

      select case when s.col is null ‘not present’ else ‘present’ end as status from primary_table as p
      left join secondary_table as s on p.key_col=s.key_col

      Reply
  • TELL HOW TO ACESS MASTER TABLE USING FOREIGN KEY

    Reply
  • the code is not providing me any output , it shows no output …does that mean there are no foreign keys in the database .

    Reply
  • it saves my time alot
    Thank you so much

    Reply

Leave a Reply

Menu