Please find the below scenario and description of the problem.
Scenario
--Step 1 creating tables
create table table_A (id int not null,category varchar(20))
create table table_B (id int not null)
GO
/*step 2 creating procedure and joining those tables.
Here you can see there is no alias name specified in select list
as there is only one column as "category"
*/
create proc dbo.proc_ambiguous_columns
as
begin
select a.id,b.id,category
from table_A a
join table_B b
on a.id = b.id
end
Go
--step 3 Executing the proc will not result in an error
exec proc_ambiguous_columns
GO
--Step 4 creating a new column with same name in table_B
alter table table_B add category varchar(20)
Go
/*step 5 Now execute the proc which will result in an error message as
"Msg 209, Level 16, State 1, Procedure proc_ambiguous_columns, Line 5
Ambiguous column name 'name'"*/
exec proc_ambiguous_columns
GO
Requirment:
Can I get list of all the procedures that are being referenced/selected the column name "category" in the final select list so that I can put the alias name to the newly added column in all the procedures.
