What are the challenges you face when working across database platforms? Take the survey
Options

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

joemombergjoemomberg Posts: 8
edited April 28, 2016 11:30AM in SQL Prompt
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

Comments

  • Options
    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
  • Options
    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!
Sign In or Register to comment.