Hi,
Today i was working with some column removal and there were lot of stored procedures that i had to verify if they are fine or not.
So, wrote the following script to check each and every stored procedure for any breakages.
Copy and Execute on your database and enjoy!!!
DECLARE @spName nvarchar(1000)
DECLARE product_cursor CURSOR FOR
SELECT CONVERT(nvarchar(20), sch.name) + ‘.’ + CONVERT(nvarchar(500), obj.Name)
FROM sys.sql_modules modu
INNER JOIN sys.objects obj
INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
ON modu.object_id = obj.object_id
WHERE obj.type = ‘P’ — for stored prcedures…
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @spName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @spName
exec sp_refreshsqlmodule @spName
FETCH NEXT FROM product_cursor INTO @spName
END
CLOSE product_cursor
DEALLOCATE product_cursor
Rizwan Ahmed 
lets say I have 5 tables in one sql database, I want to create Column ‘Name’ in Table1, is there any way to get notification that same column has been created in some other table in same database? to avoid any ambugious column exception?? any thought?
Here are my two cents…
It’s logical to have 2 columns with the same in different table. Like you would probably name PrimaryKey Column in Table1 like IdTable1 And reference it on Table2 like IdTable1. Which makes sense and easy to understand.
Now, to avoid such situations
1- Dont use select *
2- Use some script to check name of columns in overall database to see if you need to take care of that in your queries. Like Below
SELECT Table_name, Column_Name, (SELECT COUNT(*) from information_schema.columns B Where B.COLUMN_NAME = A.COLUMN_NAME) As ColumnCount from information_schema.columns A
order by column_name, ColumnCount
3- Also, another issue can be in query itself. Like if i do
Select Column_Name, * From information_schema.columns
Where Column_Name=’your value’ — this will give your error of of ambiguous
I hope that helps you.
Thanks,
Riz