Solved

Table dependency report.

Posted on 2003-10-23
6
498 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need Counts 11 43
Caste datetime 2 24
sql query Help 12 33
Syntax using Declare 3 11
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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
Via a live example, show how to shrink a transaction log file down to a reasonable size.

759 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

21 Experts available now in Live!

Get 1:1 Help Now