sql - Find all the columns and keys for multiple tables in one database

Question

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?

Answer 1

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