Solved

Get referencing tables

Posted on 2004-09-15
7
1,144 Views
Last Modified: 2008-02-01
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
Comment
Question by:Tacobell777
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 7

Accepted Solution

by:
ChrisFretwell earned 150 total points
ID: 12070750
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
 
LVL 17

Author Comment

by:Tacobell777
ID: 12070797
hmm no, the result sp_fkeys returns is more usefull to me, it contains the FKTABLE_NAME and FKCOLUMN_NAME
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 12070809
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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 17

Author Comment

by:Tacobell777
ID: 12070827
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
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 12070842
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 12073836
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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question