Generically get all related records from SQL Server Database based off of Primary Key of one table

Question

In Sql Server it is possible to visually see all relations a table has by creating a diagram of one table and right clicking and selecting 'Add Related Tables.'

Is this also possible to do for one record of the table? For example, I have table A and B are related by foreign key ab and I have table B and C are connected by FK bc. With only knowing the fk of one record from A can I get all related ab relations and bc relations generically and without knowing that the FK bc exists ahead of time?

My reason for doing this is we want to be able to pull a singular set of data from our production database to our development database so that we can debug on live data without risking changing live data. Please ask questions if this is not clear.

Answer 1

If the tables have defined foreign keys, you could query the system tables themselves, especially sys.foreign_keys (Documentation here). Here's a good query to start with, written by Stack Overflow user littlesweetseas, from this post :

SELECT 
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM 
   sys.foreign_keys AS f
INNER JOIN 
   sys.foreign_key_columns AS fc 
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE 
   OBJECT_NAME (f.referenced_object_id) = 'YourTableName'

Keep in mind that bringing down data regularly could get more difficult, as you will have to remember the order of tables, and export that data in order every time.

I would suggest finding a place to restore the production database in your test environment. The safest way to get production data into a test environment would be a database restore to your test server. You could either use the latest full backup, or take a copy-only backup of production, and restore that either to replace your test environment's database completely or to a new database. That would give you all the data and data structures from Prod, but it could also wipe out any changes or on-going tests if you replace your test database.