Table dependency report.

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
LVL 4
ckcatAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DexstarCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
Take a look at:
sp_fkeys 'tablename'
sp_pkeys 'tablename'

Anthony
0
namasi_navaretnamCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

xenon_jeCommented:
you can look in BOL at the sp_depends ....or the sp_fkeys as acperkins sugested above....
regards,
   xenon
0
arbertCommented:
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
ckcatAuthor Commented:
Thank you all for your help.

CKCAT ^_^
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.