1

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...

  • 1
    That suggestion was in the answer to the question you linked too! – Martin Smith May 31 '17 at 20:29
  • Ah. I missed that - actually tried that answers code and it didn't work – Zach Smith May 31 '17 at 20:39
2

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.

  • Added a variation of this that worked for me – Zach Smith May 31 '17 at 20:19
2

In practice, you can't because dynamic SQL is effectively impossible to predict - you can't predict what string is going to be built before it runs. Even if you try, queries can have branching logic - for example, based on parameters, you may build different SQL.

  • Thanks. I'm thinking of iterating through a list of views in the database and doing a select top 1 * on each of them. that should then throw an error where one doesn't work – Zach Smith May 31 '17 at 17:12
  • Yeah, that works fine for views, just not functions or procs. – Brent Ozar May 31 '17 at 17:15
  • which is in fact what a comment on the question i linked to suggested – Zach Smith May 31 '17 at 17:15
  • For functions and SP's, you 'might' try the technique of using sp_refreshsqlmodule - it might not catch everything, but may get you a little further - example here - stackoverflow.com/a/5555554 – Scott Hodgin May 31 '17 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.