Sunday, February 19, 2012

identifying the invalid procedure

Is there any option to identify the invalid procedure objects. Say for example if procedure P1 uses a table T1 and the table T1 is now deleted then P1 procedure will not be executed. I need to identify these procedures.
Any guidance?
sql2k suppors 'defered name resolution' thus it's perfectly legal to have a stored procedure that references a non-existent object. However, it's important to note here is that if the referenced object exists prior to the stored procedure creation, then an entry is made in sysdepends for the sproc and the object. Hence, you can use sp_depends to find dependencies.
e.g.
--a valid proc
create proc bogus
as
select *
from nonexistencetable
go

No comments:

Post a Comment