SQL Objects - dependencies

[jacktek]

When running large scripts I often get the "cannot update sysdepends table ---" message. Because of this I cannot trust the "system dependencies" as displayed for objects by Enterprise manager.
Is there a way to ask SQL Server 2000 to update the system dependencies? I would think there would be, but so far I have been unable to find it.

 

[tlbroadbent]

SQL Server will create procedures and views if referenced objects don't exist. At execution time, the compiler will resolve references. This is called deferred name resolution.

See Deferred Name Resolution and Compilation

http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_07_5wa6.asp

If you make sure objects are created in your script before objects that reference them, sysdepends wil be updated and you won't get the message. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[jacktek]

Earlier I started the below thread:
> When running large scripts I often get the "cannot >update sysdepends table ---" message. Because of this I >cannot trust the "system dependencies" as displayed for >objects by Enterprise manager.
> Is there a way to ask SQL Server 2000 to update the >system dependencies? I would think there would be, but so >far I have been unable to find it.

I attempted to be brief. As a result, I received a reply that indicated I was too brief, as Broadbent misunderstood what I was asking. Let me elaborate (smile):
1) I know about deferred resolution.
2) I know why I received the "cannot add rows to sysdepends ---" message.

I want SQL Server to tell me what objects depend on other objects. For example, if I change a field in a table from varchar to integer, I want to know what stored procedures and user functions reference that table, so that I look at them and determine whether they also need a change.
Enterprise Manager will display what objects reference that table (it will display object dependencies). However, I know that it does not display complete info to me here at my work, and I suspect it is because of the "cannot add rows to sysdepends --- " message we encounter during processing of scripts.
We are hot and heavy in developing a new system, and a LOT of scripts get generated. I ideally want to be able to mark a lot of new / updated procedures to be scripted into one script file for distribution to remote customers. Unfortunately the scripting process puts all the "if exists then drop object" statements at the front of the script, then puts all the create statements after that. I am trying to escape the tedium of having to script each object individually, then combining many separate text files into one text file to be sent to our customers. Plus this manual process increases the chance for error. PLUS, this would not correct the errors in sysdepends that already exist...

So -- I was just hoping there was a way to instruct SQL server to rebuild the sysdepends table, so that I can have my cake and eat it too.....

Thanks,

Jack Ray

 

[tlbroadbent]

The sysdepends table is updated if the objects are altered. Here is a script to help you get started on ALTERing all the views and procedures. The procedure wil create a script which can be copied to a query window and executed to ALTER the objects without really changing them. The real impact is that sysdepends get updated in the process.

--Run this query to create ALTER statements for procedures
--and views. Copy the results to a new query window and
--execute the script. The sysdepends table should be updated
--with all dependencies.

set nocount on

Select replace(text,'create view','ALTER View') + char(10) + 'GO'
From syscomments c
Join sysobjects o
on c.id=o.id
where o.type = 'v'
and encrypted=0
and left(o.name,3) Not In ('sys','dt_')
order by o.name, c.number, c.colid

Select replace(text,'create proc','ALTER PROC') + char(10) + 'GO'
From syscomments c
Join sysobjects o
on c.id=o.id
where o.type = 'p'
and encrypted=0
and left(o.name,3) Not In ('sys','dt_')
order by o.name, c.number, c.colid

Note: You should examine the resultant script to make sure the replace function worked properly. I just created the script and performed limited testing. You may find many ways to enahance it. It should give you a start toward fixing the problems of missing dependencies. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[jacktek]

The script, at first blush, appeared to work properly. However, it would only get a small amount of the actual text of the object. And on the portion that it did get, all carriage return and line feed info was dropped, so it would be all one long line..
You have been a big help, because (1) if you don't know of an easy solution to my problem then there probably isn't one, and (2) your sql gives me ideas of what I might do in the way of an automated solution (when I have the time (smile) )...
I was also impressed by my (admittedly quick) visit to your web site. I'll re-visit it later when I'm have more time.

Thanks

 

[tlbroadbent]

1) make sure the result window in Query ANalyzer is set to retrun at least 400 characters per column.
2) Don't return the result to a grid. Return to a text window in QA. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm