Server 2000 is keeping track of depedencies or perhaps the dependencies
simply need to be re-evaulated / recompiled.
What is the best and/or easiest way to ensure that the dependency info is
accurate?
Is there a way to tell MSSQL to rebuild/reexamine them all?
Thanks in advance to the MVPs and other generous and knowledgable posters
who have helped me recently.
ChadChad (chad.dokmanovich@.unisys.com) writes:
> I am under the impression that either there are deficiencies in the way
> SQL Server 2000 is keeping track of depedencies or perhaps the
> dependencies simply need to be re-evaulated / recompiled.
> What is the best and/or easiest way to ensure that the dependency info is
> accurate?
To forget about it!
Seriously, the nature of SQL Server makes it very difficult for the
server to really keep track of dependencies. If you drop a procedure and
recreate it - of course now SQL Server cannot know which procedure
that refers to it. (The same happens if you alter a procedure. Now, that
is stupid.) Also, SQL Server permits you to create a procedure that
refers to a table that does yet not exist. And you can drop and recreate
a table.
In my daily work, I often use the dependency information to get a
quick information about references to tables and individual columns.
I often have access to an empty database that I have built from scripts,
so this information should be correct. (Since my build tools loads tables
before procedures, and anyway tells me if I refer to something non-
existing.
But if I am looking for where a procedure is being referenced, I run
a tool that searchces SourceSafe. In that case I am also interested in
references from client code, so SQL Server would not be sufficient, even
if the information was correct and complete.
> Is there a way to tell MSSQL to rebuild/reexamine them all?
Not really. What you can do is to generate scripts for your database
and then build a database from that script. But since that script
would sort the objects according to the known dependency information,
you might still not get everything loaded in the right order.
(And anyway, you should have all your source code under version control.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I check dependencies in several ways.
The least effective is to check sysdepends. As Erland pointed out,
it's not usually up-to-date or complete in the face of schema changes.
There are some ways to keep it more up-to-date though. A major problem
with sysdepends is that it doesn't keep track of cross-database
references.
You can also do a text search of stored procedure code via syscomments.
The best approach for checking dependencies I have found is to simply
script out the entire database to text files, then use a text search
utility to search for table or procedure names. This is fast and
complete. I use textpad to "find in files", it works really well. I
automatically script the databases out every day to have a current
snapshot.
My dream is to have a T-SQL parser which reads through all the code and
compiles exactly which procs, tables and fields are being used and
which operations are being done on them. And compile the results into
a set of user tables. Some day.
