?
Solved

Table dependency report.

Posted on 2003-10-23
6
Medium Priority
?
512 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 200 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 600 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 600 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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 9

Assisted Solution

by:xenon_je
xenon_je earned 200 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 400 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

839 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