jump to navigation

Dependent tables April 3, 2008

Posted by msrviking in Scripts.
trackback

Yesterday, I had a query from one of my fellow workers on how to find the dependent tables of a primary key. I happen to do this script quickly for him, and he was something like "Wow!".

I haven’t yet revealed to him how got the script. Well, it is simple and I am going to share it with everyone out there.

I recollected that I had seen some results like what he wanted by running a system stored procedure. OK, so what next? I did these quickly

1) sp_helptext sp_help
2) Searched for Foreign Key keyword in the stored procedure and I couldn’t find.
    And I realized probably there could a call to a different system sp. So I scanned thro’ and found sp_helpconstraints
3) sp_helptxt sp_helpconstraint
4) Search for Foreign Key keyword, and once I found the query I tweaked around to give him more of what he needs (you see a value add Wink).

Finally the script is here, it is simple.

select 
    object_name (fk.referenced_object_id)
    , rtrim(schema_name(ObjectProperty(parent_object_id,’schemaid’))) 
     + ‘.’ + object_name(parent_object_id) 
   from sys.foreign_keys fk
    inner join sys.sysobjects so
    on fk.referenced_object_id = so.id
order by object_name(so.id)

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: