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

Find invalid objects - sproc with ref to non-existent DB

I've got a sproc which does a join to a table in another database. That DB was dropped, which caused the sproc to stop working. To identify any other ones with a similar problem, I ran the excellent 'Find Invalid Objects' tool but it didn't pick it up. Here's a simple example that represents my problem:
CREATE PROC up_MyProc
AS
    BEGIN
        SELECT  col1 ,
                col2
        FROM    MyTable t1
                INNER JOIN OtherDB.dbo.OtherTable t2 ON t1.id = t2.id;
    END;
Would it be possible for Find Invalid Objects to detect this kind of snafu?

Thanks
Joe
joemomberg
0

Comments

2 comments

Sort by
  • David Priddle
    Hi Joe,

    Which version of SQL Prompt are you using? For me, it's warning me that it can't find the table on the non-existent database:

    HbP4Dhv.png?1

    Best regards,

    David
    David Priddle
    0
  • joemomberg
    Sorry for the late reply - I hadn't updated my email address so didn't get an alert! I'm on version 7.2.0.260.
    On a related note, I ran Find Invalid Objects on a truly terrible database with 31k views and 2k tables but it just said no invalid objects immediately. I know that there are simple invalid ones - e.g. a view on a non-existent table - but these aren't picked up. I can't complain too much that a tool struggles with such a monstrous schema though!
    joemomberg
    0

Add comment

Please sign in to leave a comment.