Find Invalid objects - not finding triggers

gsemel

Hello. I'm trying to find invalid objects after moving DatabaseB to a different instance, where DatabaseB has triggers that reference DatabaseA. When A and B were on the same instance, these triggers worked fine, but now I have to change the DbA.Schema.Table references to Server.DbA.Schema.Table. The tool isn't listing any of these triggers as invalid objects. There is no DatabaseA on this new instance. Any ideas?

Thanks!

Michael Clark

Hi gsemel,

Thanks for reporting that issue -- sorry that we are no longer showing your triggers as invalid objects.

Would it be possible to confirm that using a Linked Server has caused Find Invalid Objects to stop working with your trigger?

Could you give us a little more information about how you are using Find Invalid Objects to maintain your database triggers?

Best wishes,

Michael Clark
Technical Lead, SQL Prompt

gsemel

Hi Michael. Thanks for the reply. We're trying to take two databases that have both lived on the same server and instance, and move one database to a different server/instance (one will stay on-prem and the other is simulating an offsite server/instance). Both databases have objects from the other referenced in views, sprocs, functions and triggers. Find Invalid Objects has been pretty good at finding the broken references, but some objects with broken references (triggers, views) aren't showing up in the results.

The triggers (and other objects) are nothing special, and the move to linked servers and accessing objects across linked servers shouldn't really be a factor here. My goal is to use Find Invalid Objects to find all the places where I need to update the object reference to the four part fully qualified name. When DatabaseA.dbo.TriggerA references DatabaseB.dbo.TableB, and DatabaseB has moved to a different server/instance, TriggerA should be found by the invalid objects tool, because DatabaseB.dbo.TableB no longer exists without the fourth part of the fully qualified name (the server/instance), right?

A caveat...I left DatabaseB on the same server but took it offline in SSMS. Could that be why some object are not being shown as invalid, because the database exists in its original location, and offline isn't enough to let the tool "not find" the referenced objects? I can try detaching DatabaseB and give it a shot.

Thanks for your help.

gsemel

Detaching did let the tool find the views that still had broken references. Triggers seem to always be excluded, though.