Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
A wise man will make more opportunities than he finds
 
Francis Bacon
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

05
November

How Do I find the Primary and Foreign Key fields in an SQL Database?

During a recent Crystal Reports 2011 consultancy, I was working with a bespoke SQL based database, without any information about the structure, the tables, fields or the links.

For me to troubleshoot the data issues of the reports that had been designed we needed to know how the whole database structure with regards to the tables, the fields, the primary and  foreign keys and how they linked to each other, and there was no database schema and documentation provided.


Solution

The only way to get some information about this database was to create some SQL to extract the primary and foreign key information, using the system tables:

SELECT
    opk.name AS PrimaryKey_table,
    cpk.name AS PK_column,
    pk.name AS PrimaryKey_Field,
    ofk.name AS ForeignKey_table,
    cfk.name AS FK_column,
    fk.name AS ForeignKey_Field
   
FROM sys.objects ofk
    INNER JOIN sys.foreign_keys fk
        ON ofk.object_id = fk.parent_object_id
    INNER JOIN sys.foreign_key_columns fkc
        ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns cfk
        ON fkc.parent_object_id = cfk.object_id
        AND fkc.parent_column_id = cfk.column_id
    INNER JOIN sys.columns cpk
        ON fkc.referenced_object_id = cpk.object_id
        AND fkc.referenced_column_id = cpk.column_id
    INNER JOIN sys.objects opk
        ON fk.referenced_object_id = opk.object_id
    INNER JOIN sys.key_constraints pk
        ON fk.referenced_object_id = pk.parent_object_id
        AND fk.key_index_id = pk.unique_index_id
ORDER BY opk.name, ofk.name, fkc.constraint_column_id

This SQL code enabled us to list the Primary tables and the Primary field, and their corresponding Foreign table and fields.

If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us


MAKE A COMMENT

Name *

Email Address *

Comment *