[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1158
  • Last Modified:

Get referencing tables

whats the best way to get a list of tables that reference to a table?

Is it

- sp_fkeys | if so, how do I select the FKTABLE_NAME and FKCOLUMN_NAME into a variables so I can do something with it?
- query a view, if so, which one?
0
Tacobell777
Asked:
Tacobell777
  • 3
  • 2
1 Solution
 
ChrisFretwellCommented:
sp_depends tablename

this will return views and procedures that depend on your table.

sp_helpconstraint tablename

this will return foreign keys and any other contraints on your table

You can also use the information schema views. For example INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS might have what you are looking for.

You can query the system tables directly, but this is always something that is recommended against doing.  That said, since sp_depends and sp_helpconstraint are sprocs, you can probably read through them and figure out how to get what you want in exactly the format you want

0
 
Tacobell777Author Commented:
hmm no, the result sp_fkeys returns is more usefull to me, it contains the FKTABLE_NAME and FKCOLUMN_NAME
0
 
ChrisFretwellCommented:
What did you want to do with it when you got it? We've often written routines to take the results of an sp (like spaceused) and insert them into a table (temp or permanant) then used them from there. You may want to do that, but it really depends on what you want to do with the results once you have them.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Tacobell777Author Commented:
If a user wants to delete a record (web based) then I want to display a list of objects that depend on the record.
So the user clicks "yes delete"
- see what tables depend on it
- automatically check if those tables that reference to the table have a FK record related to its PK
- if it does, then display the objects that depend on this record
- if not, then delete the record

I am not using cascading deletes here, as it does not apply.
0
 
ChrisFretwellCommented:
Ouch. I know what you want to do.Its not impossible but needs some thought. I'm sure someone will answer before I do, but I'll think on it.

I'm thinking either dynamic sql, or you write some views. Not sure yet.
0
 
Eugene ZCommented:
you can try sp:

sp_MSdependencies

see sampes
http://www.winnetmag.com/SQLServer/Article/ArticleID/21882/SQLServer_21882.html

also check:
FIX: sp_MSdependencies May Run for a Long Time to Script Tables and Views
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q308888
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now