Solved

Get referencing tables

Posted on 2004-09-15
7
1,143 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

740 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