Solved

Table dependency report.

Posted on 2003-10-23
6
504 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Stored Proc - Performance Enhancement 15 51
SSIS On fail action 5 37
SQL Server - Copy database from one server to another 3 30
TSQL Challenge... 7 35
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…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

766 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