November 10, 2005 at 2:32 am
Hey i want to get the list of dependencies that a table in one data base can be dependent on, i know there is system table to access to write a T SQL but can anyone help me out thanks
November 10, 2005 at 5:57 am
Well, here's bad news for you - there is no reliable way inside SQL Server to see all object dependencies with certainty. (sysdepends et all does not work reliably)
If you have nothing - you're in for some labor. Script out every table, view, proc, trigger etc and audit each object, looking for references. When you find one, take note of it. When you're done, you have a complete map of your db. Now, time to place this information in the one place where dependencies should be recorded - in the model.
What you need is a modelling tool that contains a maintained model of the database(s). This is also the place where dependency questions can be answered. If you don't have any or both of these, now is the time to get them. (so your work won't be in vain the next time the same question comes up)
/Kenneth
November 10, 2005 at 6:03 am
I have so far been working with these two bits of code.. again like you mentioned they are not very reliable.. but they do give me some results... The problem is am looking for depends from view's, stor proc, tables etc..
SELECT DISTINCT so1.name, so2.name FROM sysobjects so1
INNER JOIN sysdepends sd
ON so1.id = sd.id
INNER JOIN sysobjects so2
ON so2.id = sd.depid
WHERE so2.name = 'TABLENAME'
SELECT so.name FROM sysobjects so
INNER JOIN syscomments sc
ON so.id = sc.id
WHERE sc.text LIKE '%TABLENAME%'
November 10, 2005 at 6:16 am
Yes, you're on the right track. But, as you've noted, it's not 100%.
Though, what you have is about as close you can get - for the rest you need to read the actual code. In reality there is no substitute for the model when it comes to keep track of dependencies. It's where it belongs, and if maintained correctly, always accurate.
/Kenneth
November 11, 2005 at 4:42 am
If you're an application developer as well, i've always found the SQL-DMO very useful for doing this kinda thing! The "Table" object has a "Keys" collection, which can then be iterated to find FK's (key.Type = SQLDMOKey_Foreign). Pretty easy to do ... and 100% accurate in my experience!
Cheers
Vinny
November 11, 2005 at 7:26 am
This would work well, IF..... all FK's are implemented as declared foreign keys (ie that it actually exists a foreign key constraint).
Unfortunately there's nothing that prevents a column to have a logical status of a FK, but with only a unique constraint (at best) or a unique index or (worst case) no index at all (which is the case with a declared FK - you don't get any indexes with the declaration). The bottom line is, that there *may* be code lying around that depends on this kind of 'undeclared' dependency. So, it's hard to reach 100% when there may exist 'unknowns'.
/Kenneth
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply