Find SQL Objects which refer table columns that does not exists

Question

We have a legacy application and its database which has more than 7000 objects including stored procedure, views, functions, synonyms and so on.

Now we are implementing Azure DevOps, so when i import the dacpac file to the database project, i could see more number of Unresolved errors. When i check, there are more unused stored procedures and views which are referring the table columns that is not exists now.

How could i find the objects which are referring the invalid columns and tables?

Answer 1

Please check this article to find broken stored procedure using sp_refreshsqlmodule.

Answer 2

I'm not sure that you'll be able to find everything in automated way. SSDT complains to unresolved reference not only when there is broken object, but if it's using 3-4 part names as well (cross database references), so probably solution provided by @Krzysztof won't work or will work just partly.

What's needed for you to be done:

• Find all 3-4 part objects in the code
• Add all the databases needed for this code as references
• Create synonyms for each of these objects
• Use [$(server_var)].[$(database_var)].schema.object syntax in your synonyms

These steps are not so hard as can look like. You can do ~90% changes in bulk, using string replace across all the files. For example, if you are using objects from another database --> DatabaseA. So in the code you'll have only limited combinations of the possible object names:

• DatabaseA..tableName
• DatabaseA.dbo.tableName
• DatabaseA.other_schema.tableName
• [Database]..tableName
• ... and so on If you replace all these values with dbo.tableName where dbo.tableName will be the new synonym, then it should work.

Normally this process should take 1-2 days maximum.