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
|
