Find Invalid Objects not detecting ambiguous column in stored procedure

DanAvni

Using the latest SQLPrompt I have a SPROC with a query joining two tables. The SPROC is not valid in SSMS when trying to alter it with an error message

Msg 209, Level 16, State 1, Procedure GetData, Line 313 [Batch Start Line 7]
Ambiguous column name 'Guid'.

When trying to find this SPROC using "Find Invalid Objects" it shows all SPROCs are valid

Any ideas?

Jon_Kirkwood

Hi @DanAvni

Thanks for reaching out over the Redgate forums regarding your SQL Prompt query.

I have hit this myself with the Invalid Object finder not returning some expected results. 

From my understanding, the check will look at code that won't compile and also code that compiles but doesn't execute.

Ambiguous columns can sometimes fall through the cracks as the system makes assumptions about columns and that at the point of checking they do not fail.

It may be possible to tweak a couple of settings in Prompt to format code in a way to potentially pick up on these ambiguous fields

Options > Format > Styles > Qualify Object Names

Options > Inserted Code > Qualification > Qualify column names with table name

If these are enabled when you apply a code format it can add extra details to the columns which may help reduce ambiguity in stored processes, triggers etc

DanAvni

@Jon_Kirkwood, Many thanks for your answer but in my case the SPROC is not compilable when opening it for alter. It is true that fully qualified names might solve it but the purpose is to find invalid existing objects and not re-write everyting only to be able to use the tool.

I am not sure how it was implemented but if the tool would have scripted the object for create and try to create it under a temp name, it would fail and this could be detectable from code.