Solved

Get referencing tables

Posted on 2004-09-15
7
1,141 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 42

Expert Comment

by:EugeneZ
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

828 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