0

I have a very large database with over 500 tables in it. Each table contains about 100 columns with at least 5 foreign keys to other tables, as well as the the table primary key.

I am trying to link data in multiple tables, but am having difficulties as the DB owners don't have a copy of the table mappings (the tables were originally built 20 years ago).

Is there any query, or command, I can run that would produce a copy of all the columns in all the tables as well as their linkages to other tables?

Improve this question
2
  • There are queries for system tables where you can find PK and FK relationships, BUT there could (most likely are) many relationships between the tables that dont have FK's to associate them especially on a 20 year old DB. Commented Jan 5, 2021 at 17:27
  • I have a query for all columns starting fk... that have no FK select object_name(c.object_id), c.name from sys.columns c join sys.tables o on o.object_id = c.object_id where c.name like 'fk%' and not exists (select 1 from sys.foreign_key_columns fk where fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id) Commented Jan 5, 2021 at 17:36

3 Answers 3

Reset to default
2

You can use this system tables:

  • information_schema.columns
  • dbo.sysobjects

They should be accessible in such an old SQL Server.

In latest versions of SQL Server you may use:

  • sys.foreign_keys
  • sys.foreign_key_columns
  • sys.tables
  • sys.columns
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Here is query that will give you all the relationships but as I posted in comments, there most likely are many relationships between tables that just dont have keys for the associations.

SELECT o2.name AS Referenced_Table_Name, c2.name AS Referenced_Column_As_FK, o1.name AS Referencing_Table_Name, c1.name AS Referencing_Column_Name, s.name AS Constraint_name FROM sysforeignkeys fk INNER JOIN sysobjects o1 ON fk.fkeyid = o1.id INNER JOIN sysobjects o2 ON fk.rkeyid = o2.id INNER JOIN syscolumns c1 ON c1.id = o1.id AND c1.colid = fk.fkey INNER JOIN syscolumns c2 ON c2.id = o2.id AND c2.colid = fk.rkey INNER JOIN sysobjects s ON fk.constid = s.id ORDER BY o2.name
Improve this answer

1 Comment

SQL schema's missing and also the order of columns in the composite keys
1

You can start with the standard SQL ISO view INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS that links all the UNIQUE/PK constraints of the REF table to the child tables FK and then, use INFORMATION_SCHEMA.KEY_COLUMN_USAGE view, from one point (Uniqueness) to the other (FK) to links thoses columns...

As an example :

WITH T1 AS ( SELECT ROW_NUMBER() OVER(ORDER BY FK.CONSTRAINT_SCHEMA, FK.CONSTRAINT_NAME) AS RN, FK.TABLE_SCHEMA + '.' + FK.TABLE_NAME AS CHILD_TABLE_FULL_NAME, FK.CONSTRAINT_SCHEMA + '.' + FK.CONSTRAINT_NAME AS FOREIGN_KEY_FULL_NAME, STRING_AGG(FKC.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY FKC.ORDINAL_POSITION) AS FOREIGN_COLUMNS FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS REF JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK ON FK.CONSTRAINT_SCHEMA = REF.CONSTRAINT_SCHEMA AND FK.CONSTRAINT_NAME = REF.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS FKC ON FK.CONSTRAINT_SCHEMA = FKC.CONSTRAINT_SCHEMA AND FK.CONSTRAINT_NAME = FKC.CONSTRAINT_NAME GROUP BY FK.TABLE_SCHEMA, FK.TABLE_NAME, FK.CONSTRAINT_SCHEMA, FK.CONSTRAINT_NAME ), T2 AS ( SELECT ROW_NUMBER() OVER(ORDER BY FK.CONSTRAINT_SCHEMA, FK.CONSTRAINT_NAME) AS RN, STRING_AGG(UKC.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY UKC.ORDINAL_POSITION) AS REFERENCE_COLUMNS, UK.CONSTRAINT_SCHEMA + '.' + UK.CONSTRAINT_NAME AS REFERENCE_CONSTRAINT_FULL_NAME, UK.TABLE_SCHEMA + '.' + UK.TABLE_NAME AS REFERENCE_TABLE_FULL_NAME, UK.CONSTRAINT_TYPE AS UNIQUE_CONSTRAINT_TYPE_REF FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS REF JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK ON FK.CONSTRAINT_SCHEMA = REF.CONSTRAINT_SCHEMA AND FK.CONSTRAINT_NAME = REF.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS UK ON UK.CONSTRAINT_SCHEMA = REF.UNIQUE_CONSTRAINT_SCHEMA AND UK.CONSTRAINT_NAME = REF.UNIQUE_CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS UKC ON UK.CONSTRAINT_SCHEMA = UKC.CONSTRAINT_SCHEMA AND UK.CONSTRAINT_NAME = UKC.CONSTRAINT_NAME GROUP BY UK.CONSTRAINT_SCHEMA, UK.CONSTRAINT_NAME, UK.CONSTRAINT_TYPE, UK.TABLE_SCHEMA, UK.TABLE_NAME, FK.CONSTRAINT_SCHEMA, FK.CONSTRAINT_NAME ) SELECT CHILD_TABLE_FULL_NAME, FOREIGN_KEY_FULL_NAME, FOREIGN_COLUMNS, REFERENCE_COLUMNS, REFERENCE_CONSTRAINT_FULL_NAME, REFERENCE_TABLE_FULL_NAME, UNIQUE_CONSTRAINT_TYPE_REF FROM T1 JOIN T2 ON T1.RN = T2.RN
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.