0

Assuming I have the following database / tables (Fk means foreign key to said table):

Table: Brand
Table: Customer (Primary Key {CustomerId}) [Fk Brand]
Table: Address [Fk Region] [Fk Customer]
Table: Region

If I have a "CustomerId" and direct access to the database, is there a way I can loop through the database without knowing any other tables except for the table my "CustomerId" exists in?

I want to generate the sql insert statements in the correct order to re-create the entire customer structure so that anything that has a Fk to the customer and anything the customer has a Fk on, will also be re-created.

I am fine with someone just giving me a link on a "how to" to something similar. The reason I want this is because I often get a request at work saying "x customer has y issue" and sometimes without getting that exact data into a testing database it can be almost impossible to figure out as you don't want to touch the data in production. So If I can run a program that will build the customer as it exists in production, it will make my life much easier.

I am sure this must be possible using C# and or Stored Sprocs in the database.

svick
  • 236,525
  • 50
  • 385
  • 514
Landin Martens
  • 31
  • 1
  • 3

1 Answers1

0

You can get a reference to all of the tables where a column is referred to using the following:

SELECT 
     ST.Name                AS  TableName 
    ,SC.name                AS  ColumnName
FROM
    sys.columns  AS SC

    INNER JOIN sys.tables AS ST

    ON SC.Object_ID = ST.Object_Id 

WHERE
    ST.Type = 'U'
AND
    SC.name = 'CustomerId'
Richard Vivian
  • 1,700
  • 1
  • 14
  • 19
  • I am assuming this will fail though if the fk column name does not exactly match the primary key name? – Landin Martens Nov 25 '14 at 18:52
  • Is there anyway, using this to know the order in which things matter? For example, I might not be able to insert something into table Y if table X values have not been inserted into yet. – Landin Martens Nov 25 '14 at 18:56
  • You can reference the sys.constraints table to get this information. I am working on a query to test this, and will post if I can get the query completed. – Richard Vivian Nov 26 '14 at 04:52
  • There is so much I don't know about sys tables. I hope you are able to build a query of some sort. Even if it does not work 100% I would still love to see it so I can learn! – Landin Martens Nov 27 '14 at 15:07
  • Try looking at this question and answer. Give you some good points to start working with. http://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db – Richard Vivian Nov 27 '14 at 19:07