How can we help you today? How can we help you today?

Deferred Name Resolution and Command Line interface.

Fantastic :!:, a dependency tool that works out unreferenced objects that have occurred due to Deferred Name Resolution.
This has been a bugbear of mine for a long time. We use SQLCompare and DBGhost to automate our build process, however there seemed to be no way to check for missing (mistyped) views, tables or table functions due to SQL Server's Deferred Name Resolution feature.
DT to the rescue!

There is a bug, it doesn't work if you only reference the object using 1 part name. So
CREATE PROC Test
AS
SELECT * FROM dbo.TableNotExist

shows an unreferenced internal object for TableNotExist in RGDT, but
CREATE PROC Test
AS
SELECT * FROM TableNotExist

doesn't.

Obviously best practice is to use the 2 part naming convention of schema.object but this isn't always stuck to! :(

Unreferenced objects due to Deferred Name Resolution is the main reason I would use this tool, as I have most of the rest of my dependency problems sorted using a combination of SQL Compare and DBGhost. However, as I've said in another post, I would really need this to be able to work as part of an automated process.
A Command Line option to export dependency to XML would be fine. But the diagrams for me are overkill. Too slow for the schema size I work with.

The dependency algorithm is really useful. The diagrams less so.
But a great tool, none the less. :D

Thanks

Alex Weatherall
TeleWare.com
alex.weatherall
0

Comments

3 comments

Sort by
  • Bart Read
    Hi Alex,


    You've put your finger on a tricky issue there. The information I get back from our parser via the SQL Compare engine is fairly limited: basically it gives me a list of identifiers. Now this is fine, but an unreferenced 1 part identifier might often be something used only within the scope of the function (for example), so if I can't find it I assume it's just that and ignore it.

    If I didn't ignore such 1 part identifiers your diagram would be polluted with an awful lot of objects that don't exist because they're just variable names. Now I did used to emit warnings about such 1-part names into the messages that you can access by clicking on the bottom-right of the status bar, but I removed those because everybody we tested on claimed that there were too many messages (not surprising really). However, this does mean that there's no way for you to find 1-part identifiers that refer to non-existent objects.

    I'll have a chat to Dan on Monday and see if we can do anything about this. I can certainly re-enable my warning messages. The only issue then is copying the messages out from the messages dialog in bulk, which we currently don't support. Again, I'll have a chat to Dan about this and see whether or not we can get that in. You'd then be able to just run a quick search on the object name you're interested in.

    Another possibility is that I can expose the information about unresolved 1-part information in some other way for Dan to integrate into the UI, but again this is something I'm going to need to chat to him about on Monday.

    Unfortunately I can't make any promises about any of this at the moment because, as I'm sure you can imagine, we've already got a very large amount of work still to do before the final release.


    Many thanks,
    Bart Read
    0
  • alex.weatherall
    Hi Bart,

    Thought it must be something to do with how to distinguish between objects and variables, inbuilt functions etc.
    I can see how it's easier to apply the rule that all identifiers should be 2 part names so that your parser can distinguish them.

    :idea: Surely there are some basic rules you can apply to identify likely objects as opposed to variables and system functions etc.
    In short:
      1. @ sign denotes a local variable or parameter 2. # sign denotes temporary table or procedure 3. If the identifier
    myid matches a DECLARE myid within the same scope stack (i.e. BEGIN END constructs) that it is referenced then that is a local object (variable, cursor) and can be ignored. (not simple but possible)
    4. Compare the identifier name to a list of system keywords
    This list isn't exhaustive, but by applying these simple rules I think you can make your list of possible dependent objects more accurate. :?:
    I assume that when you get the identifiers, you parse all the text of each object to determine it's use and can apply certain parsing rules. I have been writing a similar parser to try and get round the problems of dependency tracking; however this was a pet project, and I've been unable to get it to a usable level yet.

    Anyway, hope you can do something to make the deferred name resolution more robust.

    Thanks,

    Alex Weatherall
    TeleWare.com
    alex.weatherall
    0
  • Bart Read
    Hi Alex,


    Those are some good suggestions and we are actually looking at incorporating some of them. There are a couple of issues though, in that the API I'm using doesn't provide me with the leading '@', '#' etc, nor does it give me any information about DECLAREd identifiers, so I'm left with trying to intelligently guess. We'll come up with something more satisfactory than the current situation though.


    Thanks,
    Bart
    Bart Read
    0

Add comment

Please sign in to leave a comment.