0

I have recently made a big change to the database and moved bunch of columns into a new schema table.

So say, [myschema].[dbo].[TableA] used to have a column called isTestData, but I've made a change to the database so that the column now exists in [Main].[dbo].[TableA] (table name is the same, but schema is different).

The problem that I am facing is that there are some stored procedures which reference isTestData from the old table ([myschema].[dbo].[TableA]). So I re-compiled every stored procedure that I have in the system so that I could get a list of stored procedures with compilation errors.

However, after running sp_recompile, I found out that there are no compilation errors being returned even though when I open up that specific stored procedure, it's got a red line in SSMS. It's compiles fine but errors out when you execute it which is expected as that column is now missing from the old table.

Is there a way to get a compilation error if column is missing?

USE [myschema]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
...
SELECT a, b, c
FROM TableA
WHERE isTestData = 0

This is the summarised stored procedure that is failing.

Thanks everyone.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Minkyu Ham
  • 1
  • 1
  • https://dba.stackexchange.com/questions/24806/disabling-checking-of-schema-on-function-stored-procedure-creation – Dale K May 16 '21 at 23:52
  • There are ways you can use programs etc to do this. But a reasonably quick, easy and relatively reliable one (in my opinion) is to make a copy of the database, script all stored procedures at once (as create procedures), delete them all then let the script run. It will tell you which ones fail. Note though that nothing I know of will find errors in dynamic SQL, if you have any. – seanb May 16 '21 at 23:52
  • 1
    sp_recompile : Causes stored procedures, triggers, and user-defined functions to be recompiled **the next time that they are run.** – lptr May 17 '21 at 00:13
  • If you can't find references in the source code control system you're using you should still find objects referencing your column with [this other SO answer](https://stackoverflow.com/a/40888667/390122) - except for encrypted functions and procedures because their code isn't revealed by `object_definition()`. – AlwaysLearning May 17 '21 at 03:09

0 Answers0