Status of SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Status of SP

Author

Topic

avishivani
Starting Member

4 Posts

Posted - 2006-01-12 : 05:06:19

Hi All,

I want to generate list of stored procedures which have become invalid because of dropped/altered tables/views.

Thanks in advance
- Avinash Shivani

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-12 : 08:08:48

If you meant unused objects, see if this helps
http://www.pinpub.com/ME2/Audiences/dirmod.asp?sid=&nm=&type=Publishing&mod=Publications%3A%3AArticle&mid=8F3A7027421841978F18BE895F87F791&AudID=3A23D70D325A41A9AC644714EED86750&tier=4&id=CE9E01AE99334E1CBDC32C150AE6BE08

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-12 : 14:29:02

You may be able to use the "sysdepends" table to get this information, but in my experience it is not maintained sufficiently relibably to give a sure-fire answer to this problem.

For example, if you create Sproc_B and then Sproc_A (which calls Sproc_B), if you then DROP, and reCREATE, Sproc_A the "sysdepends" data will be incorrect.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources