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

joemomberg

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

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:



Best regards,

David

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!