"Maria" <anonymous@discussions.microsoft.com> wrote in message
news:1b67401c44fb7$3f918f50$a001280a@phx.gbl...
> Hello!
>
> I'm in desperate need of some help. I am trying to
> identify db objects that are no longer in use. i have a
> list of sp's that are not being used, and i'm trying to
> look in the body text of the sp (by querying the text
> field in syscomments) to find references to tables and
> views. these will essentially be tables and views that are
> being referenced by unused sp's. My query looks like this:
>
> select [id] from syscomments
> where [id] = 164299745 and [text] LIKE '%TimeSeries%'
>
> The problem is: if there are any columns with a similar
> name to the table name (i.e. TimeSeriesID) this will be
> returned in my result set. there are thousands of sp's
> being returned so trawling through them is going to take
> ages. Anybody know of a quick way of finding out which
> tables and view are being referenced by a stored procedure?
>
> Any help at all will be very much appreciated!
>
> Maria

>-----Original Message-----
>Hi,
>
>You can use the system procedure SP_DEPENDS. But
References to objects
>outside the current database are not reported.
>
>use <dbname>
>go
>sp_depends <procedure_name>
>
>Sp_depends - Displays information about database object
dependencies (for
>example, the views and procedures that depend on a table
or view, and the
>tables and views that are depended on by the view or
procedure).
>
>--
>Thanks
>Hari
>MCDBA
>"Maria" <anonymous@discussions.microsoft.com> wrote in
message
>news:1b67401c44fb7$3f918f50$a001280a@phx.gbl...
>> Hello!
>>
>> I'm in desperate need of some help. I am trying to
>> identify db objects that are no longer in use. i have a
>> list of sp's that are not being used, and i'm trying to
>> look in the body text of the sp (by querying the text
>> field in syscomments) to find references to tables and
>> views. these will essentially be tables and views that
are
>> being referenced by unused sp's. My query looks like
this:
>>
>> select [id] from syscomments
>> where [id] = 164299745 and [text] LIKE '%TimeSeries%'
>>
>> The problem is: if there are any columns with a similar
>> name to the table name (i.e. TimeSeriesID) this will be
>> returned in my result set. there are thousands of sp's
>> being returned so trawling through them is going to take
>> ages. Anybody know of a quick way of finding out which
>> tables and view are being referenced by a stored
procedure?
>>
>> Any help at all will be very much appreciated!
>>
>> Maria
>
>
>.