MSSQL2005 Stored Procedures

Question

If you have many stored procedures and you change the column name of the table, is there a way to check which stored procedures will no longer work?

---

Update . I read some answers and it is clear to me that there is no easy way to do this. Would it be easier to get away from stored procedures?

Answer 1

While this will take the most work, the best way to make sure everything works is to write integration tests .

, , . , , , .

sp , . , .

. , (DDL , , ) , . , .

, . , . , , .

NUnit NDbUnit

Answer 2

SysComments :

SELECT DISTINCT Object_Name(ID) 
FROM SysComments 
WHERE text LIKE '%Table%'
AND text LIKE '%Column%'

Answer 3

.

-, sp_depends, , procs ( ), , , :

http://msdn.microsoft.com/en-us/library/ms189487.aspx

, :

• SQL: , proc, , , SQL Server , . , , .
• T-SQL: ( SQL Server), SQL Server .

SQL Server Profiler , . , , . , , "SELECT *", , .

, , , .

Answer 4

, , , :

sprocs script .

.

Answer 5

If you are only interested in finding the use of a column in a stored procedure, the best way is to do a brute force search for the column name in the sys.sql_modules definition table, where the stored procedure / function definition is stored.