April 4, 2008 at 10:18 am
Hi experts,
I'd like to extract a list of dependencies on a table and I would also like to know what those dependencies are (table named in a join or the table columns that are referenced).
Can someone please point me in the direction of where I can find this information. Thank you.
Warm regards,
April 4, 2008 at 10:34 am
This query gets you dependencies:
Select
D.id as main_object_id,
O.name as main_object_name,
O.type_desc as main_object_type,
depid as depends_on_object_id,
DO.name as depends_on_object_name,
DO.type_desc as depends_on_object_type
from
sys.sysdepends D Join
sys.all_objects O On
D.id = O.object_id Join
sys.all_objects DO on
D.depid = DO.object_id
For foreign key relationships you need to look in INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 4, 2008 at 10:41 am
This should help you list all the objects that are there in the database and their dependencies
select so1.[name] as 'objectname', so1.[xtype] as 'type', so2.[name] as 'dependson'
from sysdepends sd
inner join sysobjects so1
on sd.[id] = so1.[id]
and so1.[xtype] <> 'S'
and so1.[name] <> 'dtproperties'
inner join sysobjects so2
on sd.[depid] = so2.[id]
and so2.[xtype] <> 'S'
and so2.[name] <> 'dtproperties'
If you want only dependent tables for a specific table then add this where clause
where so2.[xtype] = 'U'
and so2.[name] = @TableName --the that you are looking for
and so1.[xtype] = 'U'
April 4, 2008 at 10:45 am
Sorry I presented a SQL 2005 solution. B's solution should wotk in 2000. The INFORMATION_SCHEMA views I mention are available in 2000 though.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 4, 2008 at 11:34 am
Of course, you should be aware that the dependency tables and info is not always up-to-date.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 7, 2008 at 8:31 am
Thank you B and everyone for your posts.
Thank you B, your post will be used as a starting point for me to learn more about the system tables.
Warm regards
April 7, 2008 at 8:45 am
rbarryoung - if you dont mind please share when the dependancy tables are getting updated, or the process for that.
April 7, 2008 at 11:13 pm
Rajan John (4/7/2008)
rbarryoung - if you dont mind please share when the dependancy tables are getting updated, or the process for that.
The only way that I know of to insure that the dependency tables are correct is to ALTER (with no change) or Recompile all objects in the database, without doing any Drops or Creates.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 8, 2008 at 2:36 am
rather than forcing the changes manually by us, do we know how sql server internally makes the changes, and how frequently, which component is responsibke for the changes?
April 8, 2008 at 10:37 pm
This is the supported way to address it. It is also, by far, the safest, most reliable and fastest way to address it, particularily before Sql2005 (which gives us modification dates).
The information is held in the aforementioned sysdepends table.
The reason that it gets out of date is primarily because the SQL Objects compiler allows deferred binding. This means that you can add and compile an object that refers to other objects that do not exist (yet). If the referenced object is added later, there is no way to fix such a dangling reference than by searching and (effectively) recompiling every other object.
And thus, the "component that is responsible for the change is the SQL objects compiler", which is why the way to fix this problem is to re-run the compiler on everything.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 9, 2008 at 8:36 am
Question for rbarryyoung: if you recompile everything, how can you insure that the children are recompiled before the parents. If the parents are recompiled first, say, because they are recompiled in alphabetical order, then you will will still have the 'dangling" problem. Please elucidate.
April 9, 2008 at 6:04 pm
curbina (4/9/2008)
Question for rbarryyoung: if you recompile everything, how can you insure that the children are recompiled before the parents. If the parents are recompiled first, say, because they are recompiled in alphabetical order, then you will will still have the 'dangling" problem. Please elucidate.
Not a problem, because the children already exist (remember, you are RE-compiling them). You don't drop them first or anything like that, just recompile them in place.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 9, 2008 at 6:19 pm
I wrote an app called sqlspec that may be of use to you. It shows dependency diagrams and pk-fk diagrams for all objects in any database on every major DBMS.
see the link in my sig for details.
---------------------------------------
elsasoft.org
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply