Removing Old/Unused Stored Procs....

Hello Friends,
Situation:
I have been charged with cleaning up a large legacy database.
During this process I have identified tables no longer used and removed them
from the schema.
Question:
I now have may stored procs that reference these tables and are no longer
needed.
How can I identify the procs and remove them programmatically?
Do I need to recompile each one and look for errors?
Any help at all would be most welcome.
Thanks in advance,
B.Hi
Use the sysobjects, sysdepends tables to identify all the procedures using
the given tables.Build a query and from the resultset u know hat objects nee
d
to be dropped.
Imtiaz
"Tam O'Shanter" wrote:

> Hello Friends,
> Situation:
> I have been charged with cleaning up a large legacy database.
> During this process I have identified tables no longer used and removed th
em
> from the schema.
> Question:
> I now have may stored procs that reference these tables and are no longer
> needed.
> How can I identify the procs and remove them programmatically?
> Do I need to recompile each one and look for errors?
> Any help at all would be most welcome.
>
> Thanks in advance,
> B.
>
>|||Cool idea.
However, what if the tables that have been removed are unknown?
Thanks in advance for any tips.
B.
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
> Hi
> Use the sysobjects, sysdepends tables to identify all the procedures using
> the given tables.Build a query and from the resultset u know hat objects
need[vbcol=seagreen]
> to be dropped.
> Imtiaz
> "Tam O'Shanter" wrote:
>
them[vbcol=seagreen]
longer[vbcol=seagreen]|||Tam O'Shanter wrote:[vbcol=seagreen]
> Cool idea.
> However, what if the tables that have been removed are unknown?
> Thanks in advance for any tips.
> B.
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:9BE5007C-30BD-465D-A9E7-C54EB5158CE9@.microsoft.com...
I was going to suggest you try and recompile all your procedures and see
which ones fail because of missing table references, but that does not
seem to work:
create table whatever (col1 int)
go
create proc whateverproc
as
select * from whatever
go
sp_depends whateverproc
go
drop table whatever
go
sp_depends whateverproc
go
drop proc whateverproc
go
If you have a list of tables you dropped, you could search syscomments
table in the database for the table names using:
Select object_name(id)
from syscomments
where text like N'%<table_name>%'
However, if your procedures are encrypted that won't work either.
--
David G.|||If you have a list of tables that you removed, then you can find out
all the stored procedures which are using the tables that were
removed.
Do a
select * from syscomments where text like '%tablename%'
this will give you a list of all the objects which were using the
table that you deleted and hence all this objects should be invalid
and hence you can delete them.
Thank You
-Pranay
"Tam O'Shanter" <Tam@.Oshanter.com> wrote in message news:<SPsWc.22207195$Id.3687174@.news.eas
ynews.com>...
> Hello Friends,
> Situation:
> I have been charged with cleaning up a large legacy database.
> During this process I have identified tables no longer used and removed th
em
> from the schema.
> Question:
> I now have may stored procs that reference these tables and are no longer
> needed.
> How can I identify the procs and remove them programmatically?
> Do I need to recompile each one and look for errors?
> Any help at all would be most welcome.
>
> Thanks in advance,
> B.