In trying to setup replication I'm finding that some of the objects (views and SPs) are outdated but were not removed from the database. So I'm getting errors along the lines of:
Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL20164)
Get help: http://help/MSSQL_REPL20164
Invalid column name 'iNoOfUnits'. (Source: MSSQLServer, Error number: 207)
Get help: http://help/207
Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL)
Get help: http://help/MSSQL_REPL
Which means i have to open up every view and function (and there are hundreds) to manually search for usage of a particular column (or possibly table) name.
Is there a way I can list all objects in a database and column references that these objects use?
I have seen this question: How to find all views with invalid column names. But I don't think it's returning columns that aren't present on the database.
But I'm wondering if there isn't a better way? How are SPs/Views/etc stored in the database? Is it text? then I would be able to select the text that contains columns that aren't present any more…
I wish the log contained the name of the object that is trying to reference an illegal column name…
===== ANSWER
The easiest way I have found, thanks to the accepted answer below, is to open an SSDT project and import the database. Then search through the solution for columns that cause subscription errors. And fix one-by-one…
Best Answer
One way of finding obsolete columns is to use SQL Server Data Tools and create a database project of the database. Import all objects from the database into this project. Once everything is imported, do a build on the database project and then it will find all the unresolved references in views, stored procedures, and functions, among other things.