Solved

Table dependency report.

Posted on 2003-10-23
6
501 Views
Last Modified: 2008-03-10
Hi, Experts:
   I am trying to try the query to go through each table in my database and list its dependencies. (Foregn Keys). Any idea how to do that?

Example:

  tbl_Cars_Info
      CarsTypeID (FK), tbl_Lookup_CarsType (PK Table), CarsTypeID (PK)
      CarsMakeID (FK), tbl_Lookup_CarsMake (PK Table), CarsMakeID (PK)
  tbl_Sales_Info
      SalesTypeID (FK), tbl_Lookup_SalesTypes (PK Table), SalesTypeID (PK)

Group by table names
   List the foreign key of the table, list the table it is depending on, list the primary key of the lookup.


Thanks a lot

CKCAT
0
Comment
Question by:ckcat
6 Comments
 
LVL 19

Accepted Solution

by:
Dexstar earned 50 total points
ID: 9610996
ckcat,
> I am trying to try the query to go through each table in my database and list
> its dependencies. (Foregn Keys). Any idea how to do that?

I have had the need to do similiar things in the past.  To do it, I use the SQL-DMO objects.  They allow you to manipulate the SQL objects programmatically, including listing out their dependent objects.  It works for related tables, but it also works for other objects too (like if a stored procedure depends on a view).

All the documentation is on MSDN, and I also found this link:
http://www.sqlteam.com/item.asp?ItemID=9093

Hope that helps,
Dex*
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 150 total points
ID: 9611008
Take a look at:
sp_fkeys 'tablename'
sp_pkeys 'tablename'

Anthony
0
 
LVL 15

Assisted Solution

by:namasi_navaretnam
namasi_navaretnam earned 150 total points
ID: 9611304
The proc below loop thru all tables and execute sp_depends, sp_fkeys, sp_pkeys on each table.


Drop Procedure sp_DependencyRep
go


Create Procedure sp_DependencyRep
AS
BEGIN

DECLARE @v_name varchar(30)

DECLARE table_cursor CURSOR FOR
select name from dbo.sysobjects
where type = 'U'

OPEN table_cursor

FETCH NEXT FROM table_cursor
INTO @v_name

WHILE @@FETCH_STATUS = 0
BEGIN

   PRINT @v_name

   EXEC ('sp_depends ' + @v_name )
   EXEC ('sp_fkeys ' + @v_name )
   EXEC ('sp_pkeys ' + @v_name )

   FETCH NEXT FROM table_cursor
   INTO @v_name

END

CLOSE TABLE_CURSOR
DEALLOCATE TABLE_CURSOR

END

go

exec sp_DependencyRep
go

HTH

Namasi Navaretnam
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 9

Assisted Solution

by:xenon_je
xenon_je earned 50 total points
ID: 9611966
you can look in BOL at the sp_depends ....or the sp_fkeys as acperkins sugested above....
regards,
   xenon
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 100 total points
ID: 9614502
Not sure why you would build some big ellaborate cursor to do a simple task:

select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE


There might be instances where everything isn't returned, but for the most part, everything can be managed using INFORMATION_SCHEMA views.

Brett
0
 
LVL 4

Author Comment

by:ckcat
ID: 9615331
Thank you all for your help.

CKCAT ^_^
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
datetime in sql 6 29
TSQL previous 5 24
Splitting the content of a column in SQL 11 21
Can someone plz fix this..getting an error 3 18
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now